Where is Table Variable Created? – Interview Question of the Week #243

Question: Are ordinary table variables created in memory or on disk? They use tempdb storage, just as temporary tables do. Their pages can be cached in memory, so “table variable” does not mean “memory-only.”

After my table-variable and temporary-table articles, readers sent me enough versions of this question that it deserved an interview answer of its own. The original answer was deliberately blunt: hard disk, not memory. The useful correction is more precise: allocation in tempdb does not mean every access goes straight to a physical disk.

Both ordinary table variables and temporary tables can have pages in the buffer pool. Both can involve tempdb I/O. They still differ in scope, transaction behavior and optimization features. Choosing one because its name sounds like a programming-language variable is a poor starting point for a performance comparison.

There is a separate feature called a memory-optimized table type. A variable declared with such a type is different from the ordinary DECLARE @T TABLE example this question addresses. Don’t use that exception to claim that every table variable is memory-optimized.

The original demonstrations are worth reading together:

• Table variable or temp table, performance comparison
• Tempdb logging explained
• Regular table or temp table
• Temp table in memory, a myth
• Is tempdb behaving like a normal database?
• Inside temp table creation
• Watching table variable data in tempdb

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.