Temporary Tables vs. Table Variables: Which One and Why

Two Different Objects, Not Two Syntaxes for the Same Thing

Both live in tempdb, but they are backed by different internal mechanics, and those mechanics — not personal preference — should drive the choice.

-- Temporary table
CREATE TABLE #Orders (OrderId INT, CustomerId INT, Total DECIMAL(10,2));
INSERT INTO #Orders SELECT OrderId, CustomerId, Total FROM dbo.Orders WHERE OrderDate >= '2026-01-01';

-- Table variable
DECLARE @Orders TABLE (OrderId INT, CustomerId INT, Total DECIMAL(10,2));
INSERT INTO @Orders SELECT OrderId, CustomerId, Total FROM dbo.Orders WHERE OrderDate >= '2026-01-01';

Syntactically almost interchangeable. Behaviorally, not close.

The Difference That Actually Matters: Statistics

A #temp table gets real column statistics, just like a permanent table — the optimizer knows (roughly) how many rows it contains and can choose a sensible join strategy. A table variable historically had no statistics at all, so the optimizer always assumed exactly one row, regardless of how many were actually inserted.

⚠ This is the classic table-variable trap: insert 500,000 rows into a @TableVariable, then join it to a large table. The optimizer, believing it holds one row, may choose a nested loop join that would be reasonable for one row and catastrophic for 500,000.

SQL Server 2019 introduced table variable deferred compilation, which closes much of this gap by compiling the statement using the actual row count discovered at first execution rather than a hard-coded guess of one. It's a real improvement — but it only helps at the point the variable is first read, and it doesn't give the optimizer the full statistical picture (density, data distribution) that a real temp table's statistics provide.

-- Check whether deferred compilation is active for your compatibility level
SELECT name, compatibility_level FROM sys.databases WHERE database_id = DB_ID();
-- Deferred compilation for table variables requires compatibility level 150 (SQL Server 2019) or higher

Transaction Log and Rollback Behavior

Table variables are not fully exempt from logging — that's a persistent myth — but they behave differently under rollback: changes to a table variable are not rolled back when the surrounding transaction rolls back, because the table variable's existence isn't tied to the transaction the same way a temp table's contents are.

BEGIN TRANSACTION;

    DECLARE @Log TABLE (Message NVARCHAR(200));
    INSERT INTO @Log VALUES ('Step 1 started');

    -- ... something fails here ...

ROLLBACK TRANSACTION;

-- @Log still contains 'Step 1 started' — useful for logging progress
-- through a transaction that might roll back, on purpose

This is occasionally exactly what you want — capturing diagnostic output that survives a rollback — and occasionally a source of confusion when someone expects a table variable to behave like everything else in the transaction.

Scope and Reusability

Aspect	#Temp table	@Table variable
Statistics	Real column statistics, updated as needed	None (pre-2019) / deferred compilation (2019+, partial)
Scope	Visible to nested procedure calls within the same session	Local to the batch/procedure only — not visible to nested calls
Indexes	Can add indexes after creation, with `CREATE INDEX`	Only what's declared inline (`PRIMARY KEY`, `UNIQUE`) — no post-hoc indexing
DDL flexibility	Can `ALTER TABLE` after creation	Schema is fixed at declaration
Rollback	Data changes roll back with the transaction	Data changes do NOT roll back with the transaction
Recompilation	Can trigger recompiles when row counts change significantly	Fewer recompiles — sometimes desirable in tight loops

A Practical Decision Guide

Small, predictable row counts (roughly under a few hundred rows), used only within a single batch: a table variable is fine and avoids recompilation overhead in hot code paths like trigger bodies or tight loops.
Anything larger, or anything the optimizer needs to join against a big table intelligently: use a #temp table. Real statistics beat a good guess.
You need an index the optimizer will actually use for a seek beyond the primary key: use a #temp table — you can add exactly the index the query needs.
You're logging progress that must survive a ROLLBACK: a table variable is the only one of the two that does this.
You're not sure and the table might grow: default to #temp. The downside of a slightly heavier object is smaller than the downside of a nested-loop-against-500K-rows plan.

When in doubt, measure it: run both versions with SET STATISTICS IO, TIME ON and compare the actual execution plan's estimated vs. actual row counts. A large gap between estimated and actual rows on a table variable is the tell-tale sign the optimizer guessed wrong.

Global Temp Tables: A Third Option, Rarely the Right One

A ##GlobalTempTable (double hash) is visible to every session, not just the one that created it, and persists until the creating session disconnects and no other session references it. This is occasionally useful for sharing state across connections during a maintenance script, but it's a narrow use case — most code that reaches for a global temp table actually wants a permanent staging table with proper cleanup, not session-scoped visibility as a side effect.

The Bottom Line

Table variables are not simply "temp tables with a different syntax," and temp tables are not simply "the safe default." The real differentiator is statistics: table variables give the optimizer little to work with (deferred compilation helps, but doesn't fully close the gap), while temp tables behave like any other table for costing purposes. Default to a temp table for anything nontrivial, reach for a table variable when the row count is genuinely small and predictable, and always check the actual execution plan when it matters.