Temp Tables vs Table Varibles: The Great Debate

There seems to be a lot of confusion around the differences between  temp tables and table variables (in the classic sense; I'm not talking about in memory table types or anything here). Some people say you should only use table variables. Some people say you should only use temp tables. Most people caution a more nuanced approach. Here I want to cover what I think are the biggest issues in an attempt to shed some light on this surprisingly tricky topic.

Statistics This is usually the most important factor when considering which one to use. It boils down to two facts, and everything else flows from these: Temp Tables maintain statistics, Table Variables do not.

Table Variables Because table variables maintain no statistics, the Query Optimizer always assumes the contain exactly one row. As a result, any joins against a table variable, unless explicitly told to behave otherwise will (probably) alwaysbe a nested loop join.

As long as the number of rows in the table variable are small…

Database Size Script

This is just a fancier version of sp_spaceused which writes the data to a temp table which you can sort on
use ManGroup
set nocount on
go

declare 
    @TableName varchar(128),
    @RID int,
    @MaxRID int,
    @SQL nvarchar(max)
if object_id('tempdb.dbo.#LoopSrc') is not null drop table #LoopSrc
create table #LoopSrc
(
    RID int identity(1,1) primary key clustered,
    TableName varchar(128)
)

if object_id('tempdb.dbo.#Tabs') is not null drop table #Tabs
create table #Tabs
(
    TableName varchar(128),
    nRows int,
    nReserved as cast(replace(sReserved, ' KB', '') as int),
    nData as cast(replace(sData, ' KB', '') as int),
    nIndexSize as cast(replace(sIndexSize, ' KB', '') as int),
    nUnused as cast(replace(sUnused, ' KB', '') as int),
    sReserved varchar(30),
    sData varchar(30),
    sIndexSize varchar(30),
    sUnused varchar(30)

)
/*****************************
*** INSERT LOOP ITEMS HERE ***
*****************************/
insert into #LoopSrc
(
    TableName
)
select name
from sys.tables

select 
    @RID = 1,
    @MaxRID = @@rowcount
/**********************
*** LOOP STRUCTURE  ***
**********************/
while @RID <= @MaxRID
    begin

        select 
            @TableName = TableName,
            @sql = 'exec sp_spaceused ' + TableName
        from #LoopSrc
        where RID = @RID
        begin try
            insert into #Tabs
            exec sp_executesql @sql
        end try 
        begin catch
        end catch

        select @RID += 1
    
    end


select *
from #Tabs
order by nRows desc

Comments

Popular posts from this blog

JOINs Using Playing Cards

Named Constraints on Temp Tables

Master Data Services on Windows 10