The usual line on table variables vs temp tables is “table variables don’t have statistics, so the optimizer assumes 1 row.” That was true once. It isn’t the whole story on a current SQL Server, and the actual gap between what the optimizer assumes and what’s really there is worth measuring rather than repeating from memory.
This post runs both side by side against the same 100,000-row dataset, on the same instance, and reads the real numbers the optimizer produced for each.
The Setup
Same data, same index, same query, loaded into a local temp table and a table variable:
SELECT TOP (100000) IDENTITY(INT,1,1) AS id, ABS(CHECKSUM(NEWID())) % 100 AS val
INTO #TempTest
FROM sys.all_columns a CROSS JOIN sys.all_columns b;
CREATE INDEX ix_temptest_val ON #TempTest(val);
DECLARE @TableVarTest TABLE (id INT, val INT INDEX ix_val NONCLUSTERED);
INSERT INTO @TableVarTest SELECT * FROM #TempTest;
val has 100 distinct values spread roughly evenly across 100,000 rows, so a WHERE val = 5 filter should return somewhere around 1,000 rows. Both objects have an index on val; the only difference is temp table vs table variable.
What the Temp Table’s Statistics Actually Look Like
DBCC SHOW_STATISTICS ('tempdb..#TempTest', ix_temptest_val) WITH STAT_HEADER;
Rows Rows Sampled Steps Density
100000 100000 100 0.0
A full histogram, sampled against all 100,000 rows, 100 steps. This is exactly what you’d expect from a permanent or temp table: real, queryable, automatically maintained statistics.
The Estimate, Side by Side
Running the identical filter against each, with SET STATISTICS PROFILE ON to capture what the optimizer actually estimated versus what really came back:
SELECT COUNT(*) FROM #TempTest WHERE val = 5;
SELECT COUNT(*) FROM @TableVarTest WHERE val = 5;
| Actual Rows | Estimated Rows | Off by | |
|---|---|---|---|
Temp table (#TempTest) |
941 | 941.0 | exact |
Table variable (@TableVarTest) |
941 | 316.2 | ~3x too low |
The temp table’s estimate is dead on, it has a real histogram to consult. The table variable’s estimate isn’t the flat “1 row” you’ll still see quoted as the rule, it’s 316.22775, which is exactly SQRT(100000). Current SQL Server versions use a square-root-based guess for an object with no statistics, a real improvement over the old fixed assumption, but it’s still off by roughly 3x against the actual row count here. Worth being precise about which behavior you’re actually dealing with: “assumes 1 row” is outdated folklore for a modern engine, “assumes roughly the square root of however many rows actually get inserted, with no visibility into the real distribution” is what’s actually happening.
Where This Actually Bites
A 3x misestimate on a simple COUNT(*) doesn’t matter, the query plan for this specific example is a cheap index seek either way. The estimate matters once it drives a bad decision further up a larger query:
- Join order. The optimizer picks join order partly based on estimated row counts. A table variable estimated at 316 rows when it actually holds 100,000 can end up on the wrong side of a join, or picked as the build input for a hash join when it should have been the probe input.
- Memory grants. A memory grant is sized off the estimate, not the actual row count. Underestimate a table variable’s size significantly and a downstream sort or hash operation can spill to
tempdbbecause it wasn’t granted enough memory to begin with, look forSORTorHASH MATCHwarnings in an actual execution plan when a query using a table variable runs slower than expected. - Parallelism decisions. A query that should go parallel based on real row counts may stay serial if the optimizer thinks a table variable involved is tiny.
None of this shows up as an error. It shows up as a query that’s mysteriously slower than the same logic against a temp table, with nothing in sys.dm_exec_query_stats obviously wrong until you check the estimate against the actual.
When Each One Is Actually the Right Choice
- Small, genuinely small, row counts (a few hundred rows or fewer) inside a single batch or simple procedure: a table variable is fine, the estimate error is small in absolute terms even when it’s large as a percentage, and table variables avoid some recompilation triggers that temp tables can cause.
- Anything that might grow past a few thousand rows, gets joined against other tables, or feeds a downstream aggregate/sort: use a temp table. Real statistics matter more than the table variable’s other advantages once row counts get large enough for the optimizer’s choices to actually matter.
- Inside a scalar or multi-statement table-valued function: table variables are often the only option pre-SQL Server 2019 inlining improvements; check actual behavior on the SQL Server version in use rather than assuming.
- When in doubt on an existing slow query: check
SET STATISTICS PROFILE ON(or the actual execution plan’s estimated vs actual rows) exactly like this post did, on the real object with real data, rather than guessing which one is the problem.
Best Practices
- Don’t repeat “table variables always assume 1 row” as a blanket rule on a current SQL Server, verify what the actual estimate is for the row counts in play, it’s a square-root-based guess now, and the gap shrinks or grows depending on the real row count.
- Compare estimated vs actual rows directly (
SET STATISTICS PROFILE ONor an actual execution plan) before assuming a table variable’s estimate is the cause of a slow query, don’t assume it from the object type alone. - Move a table variable to a temp table as a diagnostic step when a query using one is behaving worse than expected; if the plan changes for the better, the estimate was the problem.
- Add an explicit index to either object rather than relying on a full scan, both temp tables and table variables (SQL Server 2014+) support inline index definitions.
Related Scripts
- Get Temp DB Hotspots, for checking whether temp table or table variable usage is a real tempdb load contributor
- Get Query Performance Deep-Dive, for finding the actual query behind a suspected estimate problem
- Get Missing Indexes, since both objects benefit from a supporting index rather than a full scan
- Performance and Troubleshooting (area)
Leave a Reply