Temp tables vs table variables: it was never about memory

Ask why a stored procedure is slow and often enough the answer is a @table_variable holding a lot of rows. Ask why table variables are slow and you’ll hear the old folklore: “table variables live in memory, temp tables live on disk.” That’s wrong — both live in tempdb, both spill to disk under pressure, both are backed by the buffer pool. The real differences are statistics and parallelism, and they pull in opposite directions depending on how many rows you’re holding.

I benchmarked it on SQL Server 2025: load N rows into an intermediate table, then join it to a 2-million-row table — the realistic shape where the choice actually matters (plan quality dominates once N is non-trivial). Four variants of the intermediate table, N from 10 to 50,000, and the query plans captured as receipts. Methodology is the usual; numbers are ms per call, minimum of eight interleaved passes.

The contenders

#temp table — gets column statistics, gets a real row estimate, can be part of a parallel plan.
@table variable — on SQL Server 2019+ (compatibility 150) it gets deferred compilation, which gives it a real row estimate too. This is the modern default and it’s better than its reputation.
@table variable, deferred compilation disabled — the classic pre-2019 behavior: estimated at 1 row, no matter how many it holds.
@table variable + OPTION (RECOMPILE) — the old advice for fixing the estimate.

Round 1: just building and filling the table

Before any join — how expensive is it merely to create the thing and insert rows?

Rows	`#temp`	`@table`
10	0.42 ms	0.17 ms
1,000	2.68 ms	2.34 ms
50,000	16.1 ms	75.9 ms

For tiny sets the table variable is the clear winner — creating a temp table carries more fixed overhead (catalog entries, statistics scaffolding), so for a handful of rows @table is ~2.5× cheaper. This is exactly the workload table variables were designed for, and they’re good at it.

But by 50,000 rows the temp table is 4.7× faster to populate — and here’s the first receipt. The plan for the table-variable insert is stamped with a reason it ran single-threaded:

NonParallelPlanReason = "TableVariableTransactionsDoNotSupportParallelNestedTransaction"

Any statement that modifies a table variable is forced to run serially. The INSERT … SELECT that fills it can’t use multiple cores; the temp table’s can. That’s the whole gap — not memory, parallelism.

Round 2: the join — where it really bites

Now build, fill, and join the intermediate table to the 2-million-row table:

Rows	`#temp`	`@table` (deferred)	`@table` (1-row)	`@table` + RECOMPILE
10	0.52 ms	0.24 ms	0.27 ms	1.80 ms
1,000	5.9 ms	6.6 ms	5.6 ms	8.6 ms
50,000	70.7 ms	234.6 ms	205.6 ms	404.0 ms

At 10 rows the table variable still wins — the temp table’s overhead dominates, and OPTION (RECOMPILE) is actively bad (recompiling every call costs far more than the trivial query saves). At 1,000 rows everyone’s close.

At 50,000 rows the picture inverts hard: the temp table is 3.3× faster than the best table-variable variant, and the RECOMPILE version — the one people add to “fix” table variables — is the worst of the lot at nearly 6× the temp table.

The receipts, and a myth correction

Pull the row estimate and the chosen plan from each at 50,000 rows:

Variant	Row estimate on the intermediate table	Join plan	Parallel?
`#temp`	50,000 (from statistics)	Hash Match, scans big table	Yes
`@table` deferred	50,000 (deferred compilation!)	Nested Loops + seeks	No
`@table` 1-row	1	Nested Loops + seeks	No
`@table` + RECOMPILE	50,000	Nested Loops + seeks	No

Two things to read here, one of which corrects a widely-taught claim.

The classic “table variables estimate 1 row” lesson is now half-obsolete. Look at the deferred-compilation row: the estimate is a correct 50,000. On SQL Server 2019+ at compatibility level 150, table variables do get a real estimate on first compile. If you learned “table variables always think they have one row,” update that — only the disabled-deferral variant (and older versions / lower compat levels) still does that, and you can see it land a Nested Loops plan off the 1-row guess.

So why is the temp table still 3× faster with the same estimate? Because the estimate was never the only lever. The temp table’s join went parallel — a hash join scanning the big table across cores. Every table-variable variant stayed serial and the optimizer, costing a serial plan, preferred 50,000 individual index seeks over a serial scan-and-hash. Correct estimate, still serial, still slower. OPTION (RECOMPILE) doesn’t change that — it fixes estimates, not parallelism — so it just adds compile cost to a plan that was already going to be serial, which is why it came last.

So which should you use?

Table variables for small, known-small sets. A few rows to a few hundred — lookup lists, a handful of keys, the result of a tiny aggregation. They have less overhead, no statistics machinery, no recompiles, and they’re genuinely faster there. This is most of what table variables are actually used for, and it’s the right call.
Temp tables once the set gets large or you don’t know its size. They get statistics, they get accurate estimates, and — the part no hint can give a table variable — they can be filled and queried in parallel. For anything feeding a join to a big table, that’s decisive.
Don’t reach for OPTION (RECOMPILE) on a table variable as a reflex. It addresses the old estimate problem that deferred compilation already largely solved, it can’t restore parallelism, and it taxes every execution. In the large-N test it was the slowest option.
Forget “memory vs disk.” It was never the mechanism. If someone justifies a table variable choice that way, that’s the tell. The real questions are: how many rows, and does this need a parallel plan?

The honest caveats

Shared host, so trust the curves over the absolute milliseconds — but the curves are the point, and they’re stark: table variables under at small N, temp tables over at large N, crossover somewhere in the low thousands of rows on this box. One join shape, one big table; your crossover depends on row widths, indexes, and how parallel-friendly the downstream query is. There’s a real tradeoff I didn’t time here, too: temp tables can cause more procedure recompiles (from statistics changes), which matters for very high-frequency small procs — another reason small sets often belong in table variables. Measure your own intermediate sizes; the rule of thumb is just where to start looking.

Part of a series of measured T-SQL answers: temp-table cleanup, STRING_AGG vs FOR XML PATH, + vs CONCAT, ISNULL vs COALESCE, IIF vs CASE, SET vs SELECT vs DECLARE. Methodology: how to benchmark a microsecond. Harness + raw data reproducible against a disposable SQL Server 2025 container — get in touch.