Temp Tables and Table Variables

A local temp table, created with CREATE TABLE #Name, lives in tempdb and is visible only to the session that created it (and to nested procedure calls within that session), automatically dropped when the session disconnects or, if created inside a stored procedure, when that procedure ends. A global temp table, prefixed ##Name, is visible to every session on the instance and persists until the creating session disconnects and any other session currently referencing it finishes — useful for sharing intermediate data across connections, but rare in practice because it reintroduces the kind of shared mutable state that session-scoped temp tables were designed to avoid.

A table variable, declared with DECLARE @Name TABLE (...), behaves like a local variable rather than a persistent object: it is scoped strictly to the current batch or procedure (not visible to nested calls the way temp tables are), is not affected by an enclosing transaction's ROLLBACK for its data changes in older compatibility levels, and historically was assumed by the optimizer to contain exactly one row regardless of actual size, making it a poor choice for large intermediate result sets — though modern SQL Server (compatibility level 150+) added deferred compilation for table variables, which improves cardinality estimation for many cases. Table variables also cannot have nonclustered indexes created after declaration (only inline PRIMARY KEY/UNIQUE constraints defined at DECLARE time), and cannot have statistics manually updated, which further limits the optimizer's ability to pick a good plan for large ones.

As a rule of thumb, use a local temp table for anything holding more than a few hundred rows, anything you need to index explicitly or gather statistics on, or anything that needs to be visible to a nested stored procedure call; use a table variable for small, short-lived row sets inside a single batch where its narrower transactional footprint (in older compatibility modes, DML on a table variable is not rolled back by an outer transaction rollback, since it behaves more like a variable than a durable object) and lower overhead for tiny sizes are actually beneficial. Both live physically in tempdb, so heavy use of either under high concurrency can contend for tempdb resources (allocation pages, log throughput), which is why production systems with heavy temp object usage often provision multiple tempdb data files to spread that contention.