Temporary Tables and Table Variables in Microsoft SQL Server 2008 T-SQL Programming

9/23/2009

Contents

Comparison Summary

Table 7-1 contains a summary of the functionality and behavior of the different object types. Note that I don’t include global temporary tables 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.

Table 7-1. Comparison Summary

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	N/A
Logging and locking	To support transaction rollback	To support statement rollback	N/A
Statistics/recompilations/efficient plans	Yes	No	N/A
Table size	Any	Typically recommended for small tables	Any