What is the difference between a temp table, a table variable and a CTE?
Quick Answer
#temp table — a real table in tempdb; has statistics and indexes, participates in transactions, best for larger sets and multi-step work. @table variable — lighter, limited/estimated statistics (often estimates 1 row), no explicit stats, good for small sets. CTE — not materialised at all; just a named subquery for the next statement.
A #temp table is a genuine table in tempdb — it has statistics, you can index it, and it takes part in transactions, so it's the right choice for bigger intermediate result sets or multi-step processing. A @table variable is lighter but the optimiser has poor cardinality information for it — it often assumes one row — so a bad plan on a large table variable is common; I keep those for small sets. A CTE isn't materialised at all; it's just a named query the optimiser inlines, so it's for readability and recursion, not for caching a result. If I need to reference an expensive result several times I'll put it in a #temp table.
| #temp | @table var | CTE | |
|---|---|---|---|
| Stored in | tempdb | tempdb (mostly) | Nowhere — inlined |
| Statistics | Yes | No (row estimate often wrong) | N/A |
| Indexes | Yes (incl. non-clustered) | PK/unique only, inline | No |
| Transaction rollback | Affected | Not affected | N/A |
| Recompiles | Can cause them | Fewer | N/A |
| Scope | Session / batch | Batch | Next statement only |
Rule of thumb: small result (< ~100 rows) and simple use → table variable; larger sets, need indexes/stats, or referenced multiple times → #temp table; single-use readability or recursion → CTE.