Temp table vs table variable in SQL Server: which one, and why

SQL Server has two kinds of temporary row storage you can create in a procedure:

CREATE TABLE #Orders (OrderId INT PRIMARY KEY, Status VARCHAR(20));     -- temporary table
DECLARE @Orders TABLE (OrderId INT PRIMARY KEY, Status VARCHAR(20));   -- table variable

Both live in tempdb, and both disappear on their own. “Table variables are in memory” is a myth: both are written to tempdb pages, and both stay in memory while they’re small. The real differences are in what the optimizer knows about them, how they behave in a transaction, and their scope.

The examples run on SQL Server 2022 (compatibility level 160), against the SpDevDemo database.

Statistics and row estimates

This is the difference that matters for performance. Load the same 200,000 rows into each, and count the 2,000 cancelled orders:

DECLARE @o TABLE (OrderId INT PRIMARY KEY, Status VARCHAR(20));
INSERT @o SELECT OrderId, Status FROM dbo.Orders;

SELECT COUNT(*) FROM @o WHERE Status = 'Cancelled';
GO

CREATE TABLE #o (OrderId INT PRIMARY KEY, Status VARCHAR(20));
INSERT #o SELECT OrderId, Status FROM dbo.Orders;

SELECT COUNT(*) FROM #o WHERE Status = 'Cancelled';

In the actual plans of the two SELECT statements:

	Rows in the table, as the optimizer sees it	Estimated rows for Status = 'Cancelled'	Actual
Table variable	200,000	447	2,000
Temp table	200,000	2,000	2,000

• A temp table gets column statistics, created automatically the first time a query filters on a column. The optimizer knows exactly how many cancelled orders there are.
• A table variable never has column statistics. Since SQL Server 2019 (compatibility level 150 and higher), table variable deferred compilation gives the optimizer the real row count, 200,000, but for the predicate it can only guess. 447 is √200,000, its fixed guess for an equality filter without statistics.

Before SQL Server 2019, or at a lower compatibility level, it’s worse. The optimizer assumes the table variable has one row:

ALTER DATABASE SCOPED CONFIGURATION SET DEFERRED_COMPILATION_TV = OFF;   -- the pre-2019 behavior
GO

DECLARE @o TABLE (OrderId INT PRIMARY KEY, Status VARCHAR(20));
INSERT @o SELECT OrderId, Status FROM dbo.Orders;
SELECT COUNT(*) FROM @o WHERE Status = 'Cancelled';     -- estimated: 1 row
GO

ALTER DATABASE SCOPED CONFIGURATION SET DEFERRED_COMPILATION_TV = ON;

An estimate of 1 row for a table holding 200,000 leads to nested loops joins that run 200,000 times, and memory grants far too small, so sorts spill to tempdb. That’s the classic “the procedure got slow when the data grew” table variable bug.

Transactions: table variables ignore ROLLBACK

DECLARE @tv TABLE (Id INT);
CREATE TABLE #tt (Id INT);
INSERT @tv VALUES (1), (2), (3);
INSERT #tt VALUES (1), (2), (3);

BEGIN TRANSACTION;
    DELETE @tv;
    DELETE #tt;
ROLLBACK;

SELECT (SELECT COUNT(*) FROM @tv) AS table_variable_rows,
       (SELECT COUNT(*) FROM #tt) AS temp_table_rows;

table_variable_rows  temp_table_rows
-------------------  ---------------
0                    3

The rollback undid the delete on the temp table, but not on the table variable. That’s a trap if you expect it to be undone, and a feature when you want to keep something through a rollback, like the steps of a failed run you want to log afterwards (see debugging procedures).

The other differences

	#temp table	@table variable
Column statistics	Yes, automatic	Never
Row count known to the optimizer	Yes	Yes on SQL Server 2019+ (level 150+); otherwise 1
Indexes	Any, created at any time	Inline only, in the declaration: PRIMARY KEY, UNIQUE, INDEX ix (col)
Affected by ROLLBACK	Yes	No
Causes recompiles	Can, as its statistics change	No
Scope	The session, visible to procedures it calls	The batch or procedure only
ALTER TABLE, TRUNCATE	Yes	No
Parallel plans when inserting into it	Yes	No (INSERT ... SELECT into a table variable is serial)
Can be passed as a parameter	No	Yes, as a table-valued parameter, with a user-defined table type

Which one to use

• More than a few hundred rows, or joined and filtered in later queries: a temp table. The statistics are worth the occasional recompile.
• A handful of rows, used simply (a list of IDs to loop over, a few settings): a table variable is fine and avoids recompiles.
• Data that must survive a rollback: a table variable.
• Passing rows into a procedure: a table-valued parameter, which is a table variable.
• On SQL Server 2017 or older, or a database below compatibility level 150: be even more careful with table variables, because the optimizer believes they hold one row.

Finding the problem

The symptom of a table variable with too many rows is in the actual plan: the operator reading it estimates few rows and returns many, and the join above it is a nested loop with a huge number of executions. SpDev Pro flags exactly this when it profiles a procedure: a “bad estimate” finding on the line, naming the table variable and the difference between the rows SQL Server expected and the rows it got.