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…

Ambiguous IN functionality

Here is an interesting item that ended up biting me last week. This is definitely unexpected behavior from my understanding, but I wanted to make sure others were aware to avoid the problems. This code assumes you have a tally table called dbo.numbers with a single integer column named [Num].
--Set up a Temp Table so that we can limit the information we are getting out
If OBJECT_ID('tempdb..#NUMCheck') IS NOT NULL DROP TABLE #NUMCheck
Create Table #NUMCheck
(Number Int)

--a Simple IN using a Subquery
Select top 100 * from dbo.Numbers
where Num in (select Num From #NUMCheck )

--Oops, i forgot to load anything into the Table
Insert Into #NUMCheck
(Number)
Select 2
Union all Select 3
Union all Select 5
Union all select 7
Union all Select 11


--OK, try it again
Select top 100 * from dbo.Numbers
where Num in (select Num From #NUMCheck )

--?? Oh wait, the Temp Table uses Number not Num
--and how it should be written anyway
Select top 100 * from dbo.Numbers
where Num in (select Number From #NUMCheck)

--So it looks like if the Column does not exist in the Temp table, it will just pull from the FROM table
--No Comment on that the Column does not Exist
--No Error at Run time stating that the Column does not exist in the Temp table in the Sub Query

--But what if it did have the Same Name
If OBJECT_ID('tempdb..#NUMCheck2') IS NOT NULL DROP TABLE #NUMCheck2
Create Table #NUMCheck2
(Num Int)

Insert Into #NUMCheck2
(Num)
select 7
Union all Select 11

--and try it again
Select top 100 * from dbo.Numbers
where Num in (select Num From #NUMCheck2 )
--Why would that work, shouldn't it state that it was an Ambiguous column

/*
Select top 100 * 
from dbo.Numbers a
Inner Join #NUMCheck2 b
    on Num = Num
    
--well At least that Still works    
*/    


--so a slip up of the Column name in a Sub query can run without telling you it is an Issue
--Imagine this in a Delete Statement…

Comments

Popular posts from this blog

JOINs Using Playing Cards

Named Constraints on Temp Tables

Master Data Services on Windows 10