Temp Table vs. Table Variable

This is a comparison summary that I’ve shared to describe the differences between Temp Tables vs. Table Variables.  A few people have asked that I post it so here you go:

Comparison Summary

This is a summary of the functionality and behavior of the different object types. Note that global temporary tables aren’t included, because typically you use those for different purposes than the other types of temporary objects. You might find this table handy as a reference when you need to choose the appropriate temporary object type for a given task.

Functionality/Object Type Local Temp Table Table Variable Table Expression
Scope/Visibility Current and inner levels Local Batch Derived Table/CTE: Current statement View/Inline UDF: Global
Physical representation in tempdb Yes Yes No
Part of outer transaction/Affected by outer transaction rollback Yes No Yes
Logging To support transaction rollback To support statement rollback Yes
Locking Yes No Yes
Statistics/recompilations/efficient plans Yes No Yes
Table size Any Typically recommended for small tables Any

Temp tables can be altered with DDL statements but table variables can’t (so you cannot create a non-clustered index on a table variable for example). That makes every table variable a heap, or at best a table with a single, clustered index and every table variable access a table scan (or clustered index scan, which is essentially the same thing).

SQL Server creates statistics for temp tables, so the query optimizer can choose different plans for data involving temp tables. However, SQL Server does not create statistics on columns in table variables (So, the only way to access data in a table variable is through a table scan).

Now go read Paul White’s (b | t) post on Temporary Tables in Stored Procedures