Table 2-4 contains a summary of the functionality and behavior of the different object types. Note that I didn’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 2-4. Comparison Summary
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 |