Temp Tables vs Table Variables in SQL Server

T-SQL gives you two ways to stash an intermediate result set: temp tables (#orders_stage) and table variables (@orders_stage). They look interchangeable in a 10-row example and behave completely differently at 100,000 rows. The persistent myth is "table variables live in memory, temp tables live in tempdb." Both live in tempdb. The real differences are statistics, transactions, compilation, and parallelism.

The Basics

-- Temp table: visible to the session (and inner batches/procs), dropped
-- when the session ends or on explicit DROP
CREATE TABLE #orders_stage (
    order_id    int          PRIMARY KEY,
    customer_id int          NOT NULL,
    amount      decimal(10,2) NOT NULL
);
 
-- Table variable: scoped to the batch/procedure, cleaned up automatically
DECLARE @orders_stage table (
    order_id    int          PRIMARY KEY,
    customer_id int          NOT NULL,
    amount      decimal(10,2) NOT NULL
);

Both support PRIMARY KEY and UNIQUE constraints declared inline. Since SQL Server 2014 you can also declare non-unique indexes inline on both:

DECLARE @t table (
    id int PRIMARY KEY,
    customer_id int NOT NULL INDEX ix_cust NONCLUSTERED
);

The scoping rules differ in a way that matters for stored procedures: a #temp table created in a procedure is visible to procedures it calls; a table variable never leaks outside its own batch. And you can't ALTER a table variable or create indexes on it after the fact -- everything must be in the declaration.

The Difference That Dominates: Statistics

Temp tables have column statistics. Table variables have none. The optimizer builds histograms on #temp table columns exactly as it does for regular tables, so joins and filters against temp tables get real cardinality estimates.

Table variables have no statistics at all. On compatibility levels below 150, the optimizer assumed a table variable contained 1 row -- regardless of whether it held 5 or 5 million. One row means nested loops joins, serial plans, and tiny memory grants. At 5 million actual rows, that plan is a disaster: the classic symptom is a query that's fast with a #temp table and hangs with a @table variable, with the actual plan showing estimated 1 / actual 5,000,000.

SQL Server 2019 (compatibility level 150) improved this with table variable deferred compilation: statements referencing a table variable compile on first execution, using the actual row count at that moment. That fixes the 1-row absurdity but does not add statistics -- the optimizer knows how many rows, not their distribution. Selective predicates against table variable columns still get guessed. And the count captured at first compile sticks for the cached plan, which is its own parameter-sniffing-style trap when row counts vary between executions.

Rule of thumb that survives all version changes: beyond a few hundred rows, or anywhere the plan quality matters, use a temp table.

Transactions: the Behavior That Surprises Everyone

Table variables are not affected by transaction rollback:

DECLARE @audit table (msg varchar(100));
CREATE TABLE #audit (msg varchar(100));
 
BEGIN TRANSACTION;
    INSERT INTO @audit VALUES ('table variable row');
    INSERT INTO #audit VALUES ('temp table row');
ROLLBACK;
 
SELECT COUNT(*) FROM @audit;   -- 1: survived the rollback
SELECT COUNT(*) FROM #audit;   -- 0: rolled back

This is a feature, not a bug, when used deliberately: capturing diagnostic/audit rows inside a transaction that might roll back (for example, logging what an error handler saw before ROLLBACK -- see our transactions guide). It's a nasty surprise when you assumed transactional consistency for staged data.

The flip side: because table variables do minimal logging and don't participate in rollback, they take fewer locks and can be marginally cheaper for tiny, hot workloads.

Parallelism

Queries that modify table variables (INSERT/UPDATE/DELETE into @t) cannot use a parallel plan for the modification -- a documented product limitation. Reading from a table variable can be parallelized; loading one cannot. Temp tables have no such restriction, and SELECT ... INTO #t is one of the fastest ways to materialize a large intermediate set (minimally logged, parallelizable).

For large loads this alone decides the question.

Recompiles: the Case FOR Table Variables

Temp tables aren't free. Creating, populating, and dropping #temp tables inside frequently-called procedures triggers recompilations (schema and statistics changes on the temp table invalidate the plan), and under very high concurrency, tempdb allocation contention. Table variables cause fewer recompiles precisely because they have no statistics to change.

For a procedure called thousands of times per second that stages a handful of rows, a table variable is genuinely the better choice. This is the workload table variables were designed for.

Also in the table variable column: table-valued parameters (passing row sets into procedures) must be table variables -- there's no temp-table equivalent:

CREATE TYPE order_id_list AS TABLE (order_id int PRIMARY KEY);
GO
CREATE PROCEDURE process_orders @ids order_id_list READONLY
AS
SELECT o.* FROM orders AS o
JOIN @ids AS i ON i.order_id = o.order_id;

Decision Guide

Common Mistakes

Mako connects to SQL Server with AI-powered autocomplete, which helps when sketching staged queries against temp tables before committing them to a procedure. Try it free at mako.ai.