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…

View Default Constraints

A co-worker today couldn't figure out how a column, ostensibly being set to null held a value of 0 instead of null. I looked at the populating code and couldn't find anywhere a null was being replaced with a value of zero, so the most likely candidate was a default constraint on the column. Turned out he wasn't sure how to find default constraints on columns.

The easiest way is to use the sp_help function on the table name, or highlight the table name and hit alt-f1.

exec sp_help myTable

At the bottom, you'll find all the constraints on the table, including default constraints.

If however your table is not part of the dbo schema (as the Aventureworks sample database is fond of doing) you won't be able to use those methods on the table as it assumes the object exists in the 'dbo' schema. Instead, you can access the data via the Dynamic Management Views (DMVs) like this:
    TableName = object_name(c.object_id),
    DefaultConstraintName = ds.name,
    ColumnName = c.name,
    DefaultValue = ds.[definition]
from sys.default_constraints ds
inner join sys.columns c
    on ds.parent_object_id = c.object_id
        and ds.parent_column_id = c.column_id


Popular posts from this blog

JOINs Using Playing Cards

Named Constraints on Temp Tables

Master Data Services on Windows 10