The stored procedure staged matched rows into a table variable, then joined the result to a twelve-million-row transaction table. In testing it held three hundred rows and ran in nine seconds. In production, on the first month-end run, it held 180,000 rows and ran for eleven minutes, and the execution plan explained both numbers: the optimizer had estimated one row coming out of the table variable — not 180,000, not an average, literally one — and had chosen a nested loops join with the table variable as the outer side. One hundred eighty thousand executions of the inner branch later, someone noticed the plan. The fix was changing @stage to #stage. Eleven minutes became nine seconds, and nobody touched the logic.
That one-row estimate is the whole reason this topic matters, and most of the folklore around it is wrong in one direction or another. Here are the differences that actually change query behavior, current through SQL Server 2022, with the version lines drawn precisely because they moved.
Do table variables live in memory instead of tempdb?
No — both temp tables and table variables are materialized in tempdb, and the "table variables are memory-only" claim has been false since before most of us started. Both allocate pages, both can spill to disk under memory pressure, and both show up in tempdb's allocation traffic, which is why a workload heavy on either one can produce the PFS contention from my tempdb allocation contention notes. The persistent confusion comes from table variables being defined in a DECLARE statement rather than a CREATE TABLE, which feels like a programming-language variable and invites the mental model of an in-memory array. The storage engine never had that model. If your table variable holds a hundred rows, the memory-versus-disk question is academic; if it holds two million, it is writing to tempdb exactly like a temp table would.
Why does the optimizer estimate one row for a table variable?
Because table variables have no statistics, so on versions before SQL Server 2019 the optimizer compiles against a fixed guess of one row regardless of what the variable will hold at runtime. Temp tables get the full statistics machinery: the engine auto-creates column statistics on them just as it does for permanent tables, so estimates reflect reality and plans adapt as the staging data grows. This single difference decides most real-world outcomes between the two types. When the row count is small — genuinely small, dozens to a few hundred rows — the one-row guess happens to be roughly right, the plan is fine, and the table variable works beautifully. When the count can grow into the thousands or beyond, the one-row guess produces nested-loops plans that are wrong by orders of magnitude, which is the eleven-minute month-end report. The estimate problem is a special case of the cardinality estimation discipline in my statistics and estimation notes: every bad plan starts as a bad number.
SQL Server 2019, at compatibility level 150, changed this with deferred table variable compilation: the compile of a statement referencing a table variable is deferred until first execution, when the actual cardinality is known, so the estimate becomes the real row count instead of the one-row guess. It fixes the plan-shape disaster on 2019 and later. It does not add statistics — the optimizer knows how many rows there are but still nothing about value distribution within them — so skewed data can still produce a mediocre plan, just a much better-informed one.
What about recompiles and transactions?
These are the two behavioral differences people learn from incidents rather than documentation. On recompiles, the folklore has it backwards: table variables never cause recompiles because they have no statistics to invalidate, while temp tables recompile eagerly — as few as six row modifications can trigger a statement-level recompile against a small temp table, because the recompilation threshold for temporary tables is dramatically lower than for permanent ones. In a loop-heavy procedure that touches a temp table thousands of times, those recompiles are real CPU, and pre-2019 the usual band-aid was OPTION (RECOMPILE) or trace flag 2453, which made table variables recompile to pick up actual cardinalities. On 2019+ at compat 150, deferred compilation removes most of this trade-off by getting the count right without statistics.
On transactions, temp tables participate fully — DDL and DML against them roll back with the transaction — while table variables ignore transaction rollback except for statement-level atomicity. Wrap a batch in a transaction, roll it back, and your temp table changes vanish while the table variable's contents survive. This is occasionally a feature — logging rows into a table variable before the rollback that would erase a log table is a legitimate pattern — and occasionally a nasty surprise in retry logic that assumed everything reset. If your error-handling relies on rollback to clean up staging state, the temp table is the one that honors that contract.
When does each type actually win?
The decision matrix I use has four rows. Small and bounded — a lookup list, a handful of IDs, anything provably under a few hundred rows: table variable, because it is lighter on metadata churn, causes no recompiles, and the estimate guess is harmless. Large or unbounded — staging data whose volume depends on the data, not the code: temp table, because statistics and realistic estimates are non-negotiable once joins get involved, and you can index a temp table properly. Parallelism-sensitive: table variables inhibit parallel plans for the statements that modify them, so a procedure whose performance depends on parallel execution should stage through temp tables. And audit-against-rollback patterns: table variable, deliberately, for the transaction-independence. The anti-pattern to retire is choosing by habit in either direction — "always table variables, they are faster" produces the eleven-minute report, and "always temp tables" pays recompile and metadata costs in loops that never needed statistics.
One practical verification habit worth adopting regardless of type: when a procedure's performance is suspicious, get the actual execution plan and compare estimated versus actual rows on the staging object specifically. A tenfold-plus discrepancy at that operator is the signature of the estimate problem, and the parameter-shaped variant of the same disease — right count, wrong distribution — is what my parameter sniffing notes cover from the other direction.
What changed in 2019 that I should double-check on my boxes?
Deferred table variable compilation is on by default at compatibility level 150 — but compatibility level, not engine version, is the gate. An estate upgraded to SQL Server 2019 or 2022 that still runs databases at compat 130 or 140 gets the old one-row behavior, silently. Check sys.databases for compatibility_level on any database where table variables carry real row counts; upgrading the compat level is a change with its own regression risk — the cardinality estimator changed at 140 too — so test the workload rather than flipping it wholesale. When you cannot change compat level and a table variable has outgrown its welcome, the honest options are the swap to a temp table, an explicit OPTION (RECOMPILE) on the statement to get actual cardinalities per execution, or on pre-2019 builds trace flag 2453 as a scoped experiment. The swap is usually the right answer, and it is one character.
SELECT name, compatibility_level
FROM sys.databases
WHERE database_id > 4; -- user databases only
-- scoped fallback when compat level cannot move: force a fresh
-- estimate against the actual row count on every execution
SELECT c.CustomerId, c.Total
FROM @stage AS s
JOIN dbo.Customers AS c ON c.CustomerId = s.CustomerId
OPTION (RECOMPILE);Watching staging-object estimates with MonPG when SQL Server support lands
The signals that catch this family of problem are estimated-versus-actual row discrepancies per plan operator, recompile counts per procedure, and tempdb allocation rate split by workload — the three numbers that separate "the procedure is slow" from "the procedure is planning against fiction." MonPG monitors PostgreSQL in production today; SQL Server support is on the roadmap and in active development, and the SQL Server monitoring (coming soon) page carries the honest status. Until it ships, actual execution plans with row-count comparison remain the ground truth, and Query Store's capture of them is the closest thing to a standing record.