Difference between temp table vs table variable vs CTE from execution engine perspective

Temp tables are real physical tables created in TempDB. They maintain statistics, support indexing, allow accurate cardinality estimation, and are best for medium to large datasets and complex joins. They incur metadata overhead but give better execution plans.

Table variables are memory-optimized lightweight TempDB structures. They do not create full statistics and historically SQL Server assumes very few rows, so they often generate poor plans for large data. However, they minimize recompilation and are best for small datasets.

CTEs do not store data at all. They are simply query rewrite expressions that the optimizer expands inline. No TempDB usage, no statistics, no indexing, and they do not persist. Good for readability and recursion but not for reuse or performance tuning.

So, execution engine perspective:
Temp table = physical storage object
Table variable = lightweight temp object with limited optimizer insight
CTE = logical construct only, no storage

See more details

Feature	Temp Table (#Temp)	Table Variable (@Table)	CTE
Storage	TempDB (physical pages)	TempDB (mostly TempDB, minimal metadata)	No storage — Logical / Optimization only
When materialized?	Created physically before execution finishes	Created minimally then expanded	Never materialized (normally)
Logging	Logged in TempDB	Minimal logging	No logging
Index Support	Full indexing	Limited (PK/Unique/Non clustered with syntax)	None
Statistics	YES (auto stats)	NO (unless SQL 2019+ inline memory-optimized temp var improvements & recompile tricks)	Uses underlying tables' stats
Execution Plan Stability	Stable	Often assumed 1 row estimate (until SQL 2019 improvements)	Merged into execution plan
Recompilation Impact	May cause recompiles	Rarely recompiles	None
Best Use	Large datasets	Small datasets	Query readability, recursion

TEMP TABLE (#Temp Table) – Execution Internals

When we are creating a temp table below things happen internally.

SQL Engine:

Ø  Physically creates a real table in TempDB

Ø  Allocates pages + extents

Ø  Registers metadata in TempDB system catalogs

Ø  Maintains statistics

Ø  Can create indexes, constraints

Ø  Allows parallelism

Ø  Can be cached between executions (temp table caching)

Ø  Temp Table = Physical Storage + Statistics + Optimizer Friendly

It treated like a real table and query optimizer gets cardinality estimates using stats. It Can spill to disk and use better join strategies. It is best for large data and for complex joins. It supports indexing for faster execution plans. Its slight overhead to create, but faster execution phase.

TABLE VARIABLE (@Table) – Execution Internals

When we are creating a Variable table below things happen internally.

SQL Engine:

Ø  Allocates initially in memory

Ø  Still uses TempDB under the hood when large

Ø  No automatic statistics

Ø  Optimizer historically assumed 1 row

Ø  Limited indexing (PK/Unique/Non clustered only at creation time)

Ø  Does NOT support parallel insert operations

Ø  Fewer recompiles → stable but often worse plans

Ø  Table Variable =Lightweight + Limited Stats + Risky for Large Workloads

It treated more like a constant object and optimizer assumes small dataset. It is bad for large joins and bad cardinality estimation and poor plans. It is best for very small datasets (< 1000 rows ideally). Recompiles are expensive.

CTE – Execution Internals

When we are creating a CTE below things happen internally.

SQL Engine:

Ø  CTE IS NOT A STORAGE OBJECT

Ø  It does NOT create TempDB tables

Ø  It does NOT persist data

Ø  It is only a query rewrite / logical name

Ø  Execution Engine expands it inline in query plan

Ø  Optimizer rewrites it like a subquery / derived table

Ø  No statistics

Ø  No indexing

Ø  NO caching

Ø  NO benefit for reuse

Ø  CTE = Logical Expression Only + No Storage + No Stats

If used multiple times query recalculates each time CTE does not materialize (unless forced by spool). It is good for readability, recursive logic and logical structuring. It is not good when reused multiple times (use temp table instead).

Internal Memory and Logging Behavior

Aspect	Temp Table	Table Variable	CTE
Memory first	No	Yes	No
TempDB spill	Yes	Yes	N/A
Logging	Yes	Minimal	No
Catalog entry	Yes	Lightweight	None
Can persist beyond batch	Yes	No	No
Execution Engine Object Type	Physical table	Optimized temp structure	Logical inline expression