Home > Sample chapters

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

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