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