SQL Server Temporary Table vs Table Variable

Both temporary tables (#name) and table variables (@name) hold intermediate rows while you work — and picking the wrong one is a real performance lever, not a style choice. The difference comes down to statistics, indexing, transactions, and scope. This tutorial creates both against real data, then walks the trade-offs the way the optimizer sees them.

Temporary tables

CREATE TABLE #HighValue (
    OrderId INT PRIMARY KEY,
    Name    NVARCHAR(50),
    Amount  DECIMAL(10,2)
);

INSERT INTO #HighValue
SELECT o.OrderId, c.Name, o.Amount
FROM Orders o JOIN Customers c ON c.CustomerId = o.CustomerId
WHERE o.Amount > 500;

CREATE INDEX IX_HighValue_Amount ON #HighValue(Amount);   -- after load: allowed

SELECT * FROM #HighValue ORDER BY Amount DESC;

OrderId  Name   Amount
-------  -----  -------
3        Rahul  2300.00
1        Asha   1200.00
6        Meera   990.00
4        Meera   780.00

Key properties:

• Live in tempdb; visible to the whole session including procedures you call; dropped automatically when the session ends (explicit DROP TABLE #HighValue is good hygiene in long sessions).
• Have statistics. The optimizer knows how many rows are in there and how they're distributed — joins against a temp table get realistic plans.
• Indexes can be created after creation and after load (often better: load, then index).
• ##GlobalTempTables (double hash) are visible to every session — rare, mostly for debugging.

Table variables

DECLARE @Recent TABLE (
    OrderId   INT PRIMARY KEY,
    OrderDate DATE,
    Amount    DECIMAL(10,2),
    INDEX IX_Amount (Amount)          -- inline index: allowed at declaration only
);

INSERT INTO @Recent
SELECT OrderId, OrderDate, Amount FROM Orders WHERE OrderDate >= '2026-07-01';

SELECT * FROM @Recent ORDER BY OrderDate;

OrderId  OrderDate    Amount
-------  ----------   ------
5        2026-07-01   320.00
6        2026-07-15   990.00
8        2026-07-25   150.00

Key properties:

• Scoped to the batch/procedure — invisible to called procedures, gone at the end of the batch. No cleanup, no name collisions.
• Indexes only as declared inline (PRIMARY KEY, UNIQUE, and since SQL 2014 named inline indexes). Nothing can be added later.
• Not affected by transaction rollback — this is a feature: capture audit rows in a table variable, and they survive a ROLLBACK for logging in your CATCH block.
• The only table shape allowed inside functions.

The difference that decides performance: statistics

Table variables have no statistics. Historically the optimizer guessed 1 row regardless of content; SQL Server 2019's table variable deferred compilation improves this to the actual row count at first compile — better, but still no distribution histograms, and the count can be stale for reused plans.

The consequence in practice:

-- 100,000 rows in @tv, then:
SELECT ... FROM BigTable b JOIN @tv t ON ...
-- optimizer plans as if @tv were tiny → nested loops → hours instead of seconds

The same join against #temp sees real statistics and picks a hash join. This single difference is behind most "the report was fast in dev and died in prod" stories involving table variables.

Recompilation — the counterweight

Temp tables cause statement recompiles as their statistics change; table variables mostly don't. In high-frequency OLTP procedures shuffling a handful of rows, table variables' recompile-free behavior and lighter locking actually win. In batch/reporting work with row counts in the thousands+, temp tables' statistics win by miles.

Decision table

Rules of thumb:

• Row counts beyond a few hundred, or the rows get joined/aggregated afterwards → #temp.
• Tiny working sets in hot procedures, audit capture across rollbacks, inside functions → @table.
• Genuinely unsure → #temp; its failure mode (some recompiles) is far cheaper than the table variable's (catastrophic plans).

Neither: sometimes you don't need a table at all

A CTE handles one-statement pipelines without touching tempdb. Reach for materialization only when the intermediate result is reused across statements or needs an index the base query can't use. And for permanent staging shared by jobs, a real table with a cleanup policy beats abusing ##global temp tables.