Q44: What is the difference between Temporary Tables and Table Variables?
- Temporary Tables (
#TempTable): Persisted in temporary database storage (tempdb). They support statistics, secondary indexing, and multi-threaded parallel execution plans. Best for large intermediate datasets. - Table Variables (
@TableVar): Scoped to the executing transaction block or routine. They generally lack column statistics, which can lead to single-threaded or sub-optimal execution plans on larger datasets. Best for small lookup sets.
Key Takeaways
- Temporary Tables support index generation and statistics for working with large datasets efficiently.
- Table Variables work well for small temporary lookup sets without polluting temporary database storage.