A stored procedure gathers 180,000 candidate order IDs into a working set, joins that set to a two million row line item table, and aggregates. In dev it returns in under a second. In production it runs for four minutes, and the ticket lands on you. The last commit swapped the SQL Server temp table #candidates for the table variable @candidates, on the strength of a comment saying table variables live in memory and skip tempdb.
That comment is wrong on both counts, and the four minutes come from something else entirely.
Both objects allocate in tempdb
Microsoft's documentation for the table data type states it without hedging: "A table variable isn't a memory-only structure. Because a table variable might hold more data than can fit in memory, it has to have a place on disk to store data. Table variables are created in the tempdb database similar to temporary tables."
You can watch the pages get allocated. sys.dm_db_session_space_usage reports only on tempdb, and its counters update when a task ends, so put each step in its own batch.
SELECT user_objects_alloc_page_count,
internal_objects_alloc_page_count
FROM sys.dm_db_session_space_usage
WHERE session_id = @@SPID;
GO
DECLARE @tv TABLE (id INT NOT NULL, filler CHAR(500) NOT NULL);
INSERT INTO @tv (id, filler)
SELECT TOP (50000)
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),
'x'
FROM sys.all_columns AS a
CROSS JOIN sys.all_columns AS b;
GO
SELECT user_objects_alloc_page_count,
internal_objects_alloc_page_count
FROM sys.dm_db_session_space_usage
WHERE session_id = @@SPID;
GO
user_objects_alloc_page_count climbs by a few thousand pages, on the order of 25 MB of tempdb. Run the same rows through a #temp table and you get a comparable number. Both objects sit in the same database, use the same allocation structures, and compete for the same tempdb files.
The one case where the memory claim holds is a memory-optimized table variable, declared from a table type created WITH (MEMORY_OPTIMIZED = ON). Microsoft's guidance says that object "is stored only in memory, and has no component on disk" and "involves no tempdb utilization or contention." It requires at least one hash or nonclustered index on the type, and on-premises it requires a MEMORY_OPTIMIZED_DATA filegroup. In-Memory OLTP reached Standard Edition in SQL Server 2016 SP1, so this is not an Enterprise-only option any more, though Standard caps memory-optimized data at 32 GB per database. Check memory-optimized table types by edition before you design around one. It is also not what anyone means when they say DECLARE @t TABLE.
Statistics are the difference that drives everything else
The optimizer creates and maintains column statistics on a temp table. It creates none on a table variable. Microsoft's statistics documentation says so directly: "The Query Optimizer doesn't create statistics for table variables."
A temp table gets the same auto-create and auto-update machinery a permanent table gets, so how the optimizer keeps a histogram current applies to #candidates the way it applies to dbo.Orders. A table variable sits outside that machinery from the first row to the last.
Build both and compare. This runs in any user database on SQL Server 2012 or later.
IF OBJECT_ID('dbo.OrderLine', 'U') IS NOT NULL DROP TABLE dbo.OrderLine;
CREATE TABLE dbo.OrderLine
(
order_id INT NOT NULL,
sku INT NOT NULL,
qty INT NOT NULL
);
INSERT INTO dbo.OrderLine (order_id, sku, qty)
SELECT TOP (2000000)
(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) % 500000) + 1,
ABS(CHECKSUM(NEWID())) % 1000,
1
FROM sys.all_columns AS a
CROSS JOIN sys.all_columns AS b
CROSS JOIN sys.all_columns AS c;
CREATE CLUSTERED INDEX cx_OrderLine ON dbo.OrderLine (order_id);
Now the working set. A temp table survives to the end of the session, so it gets its own batch.
IF OBJECT_ID('tempdb..#candidates', 'U') IS NOT NULL DROP TABLE #candidates;
CREATE TABLE #candidates (order_id INT NOT NULL PRIMARY KEY CLUSTERED);
INSERT INTO #candidates (order_id)
SELECT TOP (180000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.all_columns AS a CROSS JOIN sys.all_columns AS b;
GO
A table variable dies at the batch boundary. Its DECLARE, its load, and the query that reads it have to sit in the same batch, or you get Msg 1087, "Must declare the table variable." Every table variable demo below follows that rule.
Turn on the actual execution plan (Ctrl+M in SSMS), then run both.
SELECT COUNT_BIG(*)
FROM #candidates AS c
JOIN dbo.OrderLine AS ol ON ol.order_id = c.order_id;
GO
DECLARE @candidates TABLE (order_id INT NOT NULL PRIMARY KEY CLUSTERED);
INSERT INTO @candidates (order_id)
SELECT TOP (180000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.all_columns AS a CROSS JOIN sys.all_columns AS b;
SELECT COUNT_BIG(*)
FROM @candidates AS c
JOIN dbo.OrderLine AS ol ON ol.order_id = c.order_id;
GO
Click the scan on the temp table side and read its properties: Estimated Number of Rows 180,000, Actual Number of Rows 180,000. The optimizer costs a hash join against two million rows and builds for it.
Click the scan on the table variable side. Estimated Number of Rows 1. Actual Number of Rows 180,000. From that single-row estimate the optimizer picks a nested loops join and a serial plan with a tiny memory grant, then executes 180,000 index seeks into dbo.OrderLine. The plan is not wrong for the data the optimizer was told about. It was told about one row.
That gap is where the four minutes come from. The scenario at the top is illustrative and the 180,000-to-1 ratio is not a slowdown factor, so measure your own before you quote a number to anyone.
The one-row estimate and what SQL Server 2019 changed
Before SQL Server 2019, a statement referencing a table variable compiled before the variable held any rows, so the optimizer used a fixed guess. Microsoft describes the consequence: "table variables don't have distribution statistics. They don't trigger recompiles. In many cases, the optimizer builds a query plan on the assumption that the table variable has no rows."
SQL Server 2019 at database compatibility level 150 added table variable deferred compilation. Compilation of the statement that references the table variable waits until first execution, at which point the optimizer reads the real row count and propagates it downstream.
Two things the feature does not do, both stated in Microsoft's intelligent query processing documentation. It does not add column statistics: "this feature doesn't add column statistics to table variables." And it does not raise recompilation frequency, so the plan built from that first execution's row count stays in cache and serves later calls until something evicts it.
Watch the estimate move:
ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 140;
GO
DECLARE @candidates TABLE (order_id INT NOT NULL PRIMARY KEY CLUSTERED);
INSERT INTO @candidates (order_id)
SELECT TOP (180000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.all_columns AS a CROSS JOIN sys.all_columns AS b;
SELECT COUNT_BIG(*) -- estimated rows on the table variable scan: 1
FROM @candidates AS c
JOIN dbo.OrderLine AS ol ON ol.order_id = c.order_id;
GO
ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 150;
GO
DECLARE @candidates TABLE (order_id INT NOT NULL PRIMARY KEY CLUSTERED);
INSERT INTO @candidates (order_id)
SELECT TOP (180000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.all_columns AS a CROSS JOIN sys.all_columns AS b;
SELECT COUNT_BIG(*) -- estimated rows on the table variable scan: 180000
FROM @candidates AS c
JOIN dbo.OrderLine AS ol ON ol.order_id = c.order_id;
GO
Each batch declares its own table variable, which keeps the second run from reading an estimate off a plan compiled under the old compatibility level. Changing compatibility level does not by itself force a fresh compile of a statement already in cache.
You can switch the feature off without touching compatibility level:
ALTER DATABASE SCOPED CONFIGURATION SET DEFERRED_COMPILATION_TV = OFF;
Or for one statement:
DECLARE @candidates TABLE (order_id INT NOT NULL PRIMARY KEY CLUSTERED);
INSERT INTO @candidates (order_id)
SELECT TOP (180000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.all_columns AS a CROSS JOIN sys.all_columns AS b;
SELECT COUNT_BIG(*)
FROM @candidates AS c
JOIN dbo.OrderLine AS ol ON ol.order_id = c.order_id
OPTION (USE HINT('DISABLE_DEFERRED_COMPILATION_TV'));
GO
Two traps follow from the missing column statistics. First, add a predicate on a column inside the table variable and the row count is right while the selectivity is a fixed guess, because there is no histogram to consult. Second, the cached plan carries the row count from the first execution. Microsoft's own caveat: "Performance might not be improved by this feature if the table variable row count varies significantly across executions." A procedure that loads 12 rows on Monday and 900,000 on Friday now has a parameter-sniffing-shaped problem in a new place.
The pre-2019 workaround
On compatibility level 140 or lower, OPTION (RECOMPILE) forces a compile at execution time, when the table variable already holds its rows. The optimizer reads the real cardinality. Microsoft's table variable documentation recommends this: "For queries that join the table variable with other tables, use the RECOMPILE hint, which causes the optimizer to use the correct cardinality for the table variable."
DECLARE @candidates TABLE (order_id INT NOT NULL PRIMARY KEY CLUSTERED);
INSERT INTO @candidates (order_id)
SELECT TOP (180000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.all_columns AS a CROSS JOIN sys.all_columns AS b;
SELECT COUNT_BIG(*)
FROM @candidates AS c
JOIN dbo.OrderLine AS ol ON ol.order_id = c.order_id
OPTION (RECOMPILE);
GO
The cost is a compile on every execution and no plan reuse for that statement. On a procedure called forty times a second, that compile cost shows up as CPU. On a nightly batch, nobody notices.
Recompiles and temp table caching
Temp tables trigger statistics-driven recompiles. The thresholds are in Microsoft's statistics documentation, and temp tables get their own row:
| Table type | Cardinality (n) | Recompilation threshold |
|---|---|---|
| Temporary | n < 6 | 6 |
| Temporary | 6 <= n <= 500 | 500 |
| Permanent | n <= 500 | 500 |
| Either | n > 500 | 500 + (0.20 * n), and from compatibility level 130, MIN(500 + 0.20 * n, SQRT(1000 * n)) |
Read the first row and the reason small temp tables recompile becomes clear. A procedure that loads 6 rows into a temp table and then reads it back crosses the 6-modification threshold on every call. Five rows sits under it, and the next insert on the same object pushes the running modification count over. OPTION (KEEP PLAN) raises the temp table thresholds to match permanent tables, which is the documented purpose of the hint: "Changes the recompilation thresholds for temporary tables, and makes them identical to the thresholds for permanent tables."
Table variables never recompile for cardinality, which is part of why the plan can stay wrong for weeks.
The other half of the story is temp table caching. SQL Server caches the metadata for a temp table created inside a module, keeping one IAM page and one data page instead of dropping and recreating the object on every call. Paul White's catalogue of what breaks caching is the reference list: named constraints, DDL after the object is created, a module defined WITH RECOMPILE, a call using EXECUTE ... WITH RECOMPILE, objects created in dynamic SQL, and manual CREATE STATISTICS.
The named constraint case bites people who follow a naming standard:
CREATE OR ALTER PROCEDURE dbo.CachedTemp
AS
BEGIN
CREATE TABLE #c (id INT NOT NULL PRIMARY KEY); -- caches
INSERT INTO #c (id) VALUES (1);
END;
GO
CREATE OR ALTER PROCEDURE dbo.NotCachedTemp
AS
BEGIN
CREATE TABLE #n (id INT NOT NULL CONSTRAINT PK_n PRIMARY KEY); -- does not cache
INSERT INTO #n (id) VALUES (1);
END;
GO
CREATE OR ALTER PROCEDURE needs SQL Server 2016 SP1 or later. Watch the cache with:
SELECT *
FROM sys.dm_os_memory_cache_counters
WHERE [name] = N'Temporary Tables & Table Variables';
Losing temp table caching in a procedure called thousands of times a minute drives create-and-drop traffic through tempdb. Two different waits come out of that, and people conflate them. Contention on the allocation bitmaps, 2:1:1 (PFS), 2:1:2 (GAM), and 2:1:3 (SGAM), shows up as PAGELATCH_UP. Paul Randal is blunt about the boundary: "allocation bitmap contention is only for PAGELATCH_UP waits." Contention on the tempdb system tables, which is what losing caching hits hardest, shows up as PAGELATCH_EX on ordinary pages in database 2. Check the page number in the wait resource before you decide which problem you have. Table variables also participate in this cache.
Indexes on temp tables and table variables
Temp tables take the full set: CREATE INDEX after the fact, filtered indexes, included columns, clustered and nonclustered columnstore, and manual CREATE STATISTICS. Each of those, applied after the object exists, counts as DDL and takes the object out of the temp table cache. Since SQL Server 2014 the inline INDEX clause works on #temp tables as well, which gets you the index without the DDL, so use it where the index shape allows and keep the caching.
One version note on columnstore: if you turned on memory-optimized tempdb metadata in SQL Server 2019 or later, Microsoft's tempdb documentation states that "Columnstore indexes can't be created on temporary tables when Memory-optimized TempDB metadata is enabled."
Table variables cannot be altered and cannot be the target of SELECT ... INTO. Since SQL Server 2014 you declare indexes inline with the definition:
DECLARE @candidates TABLE
(
order_id INT NOT NULL PRIMARY KEY CLUSTERED,
customer_id INT NOT NULL INDEX ix_customer NONCLUSTERED,
amount DECIMAL(10,2) NOT NULL
);
That index still has no statistics behind it. It gives the optimizer a seek structure and an ordering, not a distribution.
Transaction rollback does not touch a table variable
Microsoft states the rule: "Because table variables have limited scope and aren't part of the persistent database, transaction rollbacks don't affect them." Run it.
IF OBJECT_ID('tempdb..#t', 'U') IS NOT NULL DROP TABLE #t;
CREATE TABLE #t (id INT);
DECLARE @t TABLE (id INT);
BEGIN TRANSACTION;
INSERT INTO #t (id) VALUES (1), (2), (3);
INSERT INTO @t (id) VALUES (1), (2), (3);
ROLLBACK TRANSACTION;
SELECT 'temp table' AS object_type, COUNT(*) AS rows_surviving FROM #t
UNION ALL
SELECT 'table variable', COUNT(*) FROM @t;
The temp table reports 0. The table variable reports 3.
This surprises people who expected a clean rollback, and it makes the table variable the standard choice for error logging inside a TRY/CATCH block. Collect your rejected rows into @errors, roll the transaction back, and the rows are still there to write to a permanent audit table. An autonomous connection through a loopback linked server survives a rollback too, at the price of a second connection and a second set of permissions.
Parallelism and the table variable restriction
Microsoft's table variable documentation: "Queries that modify table variables don't generate parallel query execution plans." Reads against a table variable can still go parallel. The load itself runs on one thread.
Temp tables get parallel loads. SELECT ... INTO #temp has been able to run its insert in parallel since SQL Server 2014. INSERT ... SELECT into a #temp table needs the TABLOCK hint and database compatibility level 130 or higher. The target has to be a heap or a clustered columnstore index, and identity columns, triggers, foreign keys, and an OUTPUT clause each take the parallel insert away.
The #candidates table above carries a clustered primary key, which disqualifies it. Build a heap for this one:
IF OBJECT_ID('tempdb..#sku_orders', 'U') IS NOT NULL DROP TABLE #sku_orders;
CREATE TABLE #sku_orders (order_id INT NOT NULL); -- heap, no key
INSERT INTO #sku_orders WITH (TABLOCK) (order_id)
SELECT ol.order_id FROM dbo.OrderLine AS ol WHERE ol.sku = 42;
Click the insert operator in the plan. If it sits outside the parallel zone, one of the disqualifiers above is in play.
Loading eight million rows into a table variable is a single-threaded operation no hint will fix.
Stop repeating these two
"Table variables live in memory." They allocate pages in tempdb. Both objects sit in the buffer pool while they are hot, and both spill to disk under memory pressure. Nothing about a table variable avoids tempdb unless it is a memory-optimized table variable, which requires a table type you had to create in advance.
"Table variables are faster for small row counts." The row count is not what makes the difference. Load 40 rows into each, read them straight back with no join, and on the instances I have measured the two come out close enough that the difference disappears into run-to-run noise. Take those same 40 rows and join them to a partitioned fact table and a table variable can still produce a bad plan, because on anything below compatibility level 150 the estimate of 1 pushes the optimizer toward a loop join and a memory grant sized for nothing. What matters is whether the optimizer needs a cardinality estimate to make a decision.
When a table variable is right anyway
- Rows must survive a rollback. This and the UDF case below are the two hard requirements in the list. The rest are judgement calls.
- Inside a multi-statement table-valued function or a scalar UDF, where temp tables are not allowed.
- A table-valued parameter. A TVP is a table variable, so it carries no column statistics and predicate selectivity on its columns comes from a fixed guess. Microsoft states it plainly: "SQL Server does not maintain statistics on columns of table-valued parameters." The row count behaves differently from a plain table variable, though. The caller fills the TVP before the batch compiles, so the optimizer reads the real count and sniffs it, which Erik Darling demonstrated: a plan first compiled for a 100,000-row TVP keeps that estimate until something recompiles it. You get a parameter sniffing problem rather than a one-row guess.
OPTION (RECOMPILE)remains the fix, for that reason. - A handful of rows fed straight into an
INSERTor aMERGEwith no join to a large table. Past a handful, staging the source set before an upsert belongs in a temp table, so the merge plans against a real row count instead of a guess. - A high-frequency procedure where you measured the recompile cost of a temp table and found it dominant.
Temp table vs table variable decision table
| Requirement | Temp table | Table variable |
|---|---|---|
| Column statistics | Yes | No, at any version |
| Cardinality estimate | From statistics | 1 before 2019; real row count at first compile from 2019 at CL 150 |
Survives ROLLBACK |
No | Yes |
| Indexes after creation | Yes, plus filtered and columnstore, at the cost of caching | No, inline only (2014+) |
ALTER TABLE |
Yes | No |
SELECT ... INTO target |
Yes | No |
| Parallel modification | Yes, into a heap or clustered columnstore, with TABLOCK and CL 130+ |
No |
| Statistics-driven recompiles | Yes, at the temp table thresholds | No |
| Object caching in a module | Yes, with the named-constraint and DDL caveats | Yes |
| Usable in a UDF | No | Yes |
| Visible to nested scopes | Yes | No |
How to pick, in order
- Do the rows have to outlive a
ROLLBACK, or is this a UDF? Table variable. Stop. - Will the object be joined to a table with more than a few thousand rows, or filtered by a predicate the optimizer has to estimate? Temp table. Stop.
- Will it hold more than about 100 rows? Temp table. This is Microsoft's own threshold in the
tabledocumentation. - Under 100 rows, no meaningful join, called at high frequency? Table variable is a reasonable call, and on 2019 at CL 150 it starts from the right row count.
- On compatibility level 140 or lower and stuck with a table variable in a join, add
OPTION (RECOMPILE), then measure what the compile costs. ReadCompileCPUandCompileTimeoff theQueryPlanelement in the plan XML, both in milliseconds, or watchSQL Compilations/secunderSQLServer:SQL Statistics. Do not reach forsys.dm_exec_query_stats.total_worker_timehere: Microsoft defines it as CPU "consumed by executions of this plan since it was compiled," so it excludes the compilation you are trying to price, and it is in microseconds. - Whichever you pick, read the estimated and actual row counts off the plan before you close the ticket. Reading the plan before you pick an object is one step inside a wider triage that starts with what the server is waiting on, and the object choice is rarely the only thing wrong.
If you would rather hand this off, it is the kind of work I do. See SQL Server DBA services, review client results, or get in touch.