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…

Computed OBJECT_NAME column

I was writing a script to pull a bunch of meta data to dynamically generate some other scripts, and I built a temp table to house various tables and different dynamic sql strings I was planning to use. For whatever reason, I opted to make the PK the object_id and build a computed column to output the name. When I did this, I got an odd result though. Assuming you have AdventureWorks2012 installed (it doesn't need to be that version, you just need some table objects), run the following code:
use adventureworks2012

declare @tables table
    TableId int primary key clustered,
    TableName as object_name(TableID)

insert into @Tables (TableId)
select top 10 object_id
from sys.tables

select TableId, ComputedTableName = TableName, OnTheFlyTableName = object_name(TableId)
from @Tables
You should see something that looks like this: The problem is that when you declare the computed column on @tables, it assumes tempdb as the context for the object_name() function call. So even if you're currently using AdventureWorks2012, that object_name call is always made against tempdb. To fix the issue, you can add a second optional parameter to the object_name() function call with the database you want it to search against.
declare @tables table
    TableId int primary key clustered,
    TableName as object_name(TableID, db_id('AdventureWorks2012'))


Popular posts from this blog

JOINs Using Playing Cards

Named Constraints on Temp Tables

Master Data Services on Windows 10