A temporary table (#temp) is a real table in tempdb with statistics and indexes; a table variable (@table) is lighter but carries no statistics, so SQL Server typically estimates it as a single row.
For small result sets either is fine. For larger sets, a temp table usually performs better, because the optimizer can estimate its size and index it — the table variable's one-row estimate is the classic cause of a bad plan.
The temp-table-vs-table-variable choice looks like a style preference, but it changes how the optimizer reasons about your query. Understanding why comes down to one word: statistics.
The core difference
Both store their data in tempdb, but they are not equivalent:
- Temporary table (
#temp) — a real table. It supports statistics, indexes, and constraints, and the optimizer can estimate its row counts. - Table variable (
@table) — a lighter, batch-scoped variable. It supports primary-key and unique constraints but no column statistics.
The big one: statistics and estimation
Table variables have no statistics, so the cardinality estimator has no idea how many rows they hold — and generally assumes one. On a table variable holding five rows that is harmless. On one holding five million rows joined to other tables, that one-row estimate produces a plan built for a tiny set — the wrong join type, too little memory, and a likely spill. As the community guidance puts it, replacing the table variable with a temp table is often the one-line fix.
This is exactly the cardinality-estimation problem in miniature, and it surfaces as an estimate-vs-actual gap in the execution plan — often alongside a tempdb spill.
When to use each
| Use a table variable when… | Use a temp table when… |
|---|---|
| The set is small (a few rows) | The set is large |
| Logic is simple; no stats/index needed | You need indexes for fast access |
| You want minimal logging overhead | Accurate cardinality estimation matters (e.g., joins) |
| It is used in an inline function or with READONLY params | You will run multiple passes over the data |
The practical default: temporary tables are the safer, more reliable choice for anything beyond small result sets. If you must keep a table variable on a larger set, OPTION (RECOMPILE) on the statement that reads it can let the optimizer see its actual row count at runtime.
Why this matters to optimization
A table variable used on a large set inside a slow stored procedure is a recognizable pattern: the plan shows a one-row estimate against millions of actual rows. An AI analysis can flag exactly that and recommend the temp-table swap (or a recompile). SprocOptimizer reasons over the plan and the estimate gaps as part of its analysis, and validates any such rewrite for identical results before it is promoted.
Frequently asked questions
A temporary table (#temp) is a real table in tempdb that supports statistics, indexes, and constraints, so the optimizer can estimate its row counts and seek it. A table variable (@table) is lighter and scoped to the batch, but it carries no statistics, so SQL Server generally estimates it as a single row. Both store data in tempdb.
It depends on the data size. Table variables can be faster for very small result sets and simple operations, but for larger sets temporary tables usually win because they have statistics and can be indexed, letting the optimizer build an efficient plan. The most common performance problem is using a table variable for a large set, where the one-row estimate leads to a poor join plan.
No. Table variables do not maintain column statistics, so the cardinality estimator has no information about their contents and typically assumes one row. Temporary tables do have statistics, just like regular tables. This is the single biggest reason a table variable can produce a worse plan than a temp table on larger data.
Use a table variable for small amounts of data and simple logic that does not need statistics, indexes, or locking. Use a temporary table when working with larger data sets, when you need indexes for fast access, or when accurate cardinality estimation matters for joins. As a default, temporary tables are the safer, more reliable choice for anything but small result sets.
Primary sources & further reading
- MSSQLTips — Temp Table vs Table Variable Performance Testing.
- SQLShack — When to Use Temporary Tables vs Table Variables.
- Redgate — Choosing Between Table Variables and Temporary Tables.
Catch the 1-row estimate trap
SprocOptimizer reads the plan and the estimate gaps to spot a table variable hurting a large query, and validates the fix — on-premises, with no row-level data leaving your network.
Request a Demo Read the Optimization Guide