Temp Tables vs Table Variables vs CTEs
Temp Tables vs Table Variables vs CTEs
Choosing the right intermediate structure depends on dataset size, indexing requirements, and scope lifetime.
Comparison Table
| Feature | Temporary Table | Table Variable | CTE (WITH clause) |
|---|---|---|---|
| Physical Storage | TempDB / Disk | Memory / TempDB | Inlined / Transient |
| Scope | Session / Connection | Batch / Stored Proc | Single Statement |
| Indexes | Supported (Full) | Primary Key only | Not supported |
| Statistics | Yes (Optimizer tuned) | No statistics | Uses base statistics |
| Recursion | No | No | Supported (RECURSIVE) |
| Recommended Size | Large (> 10k rows) | Small (< 1k rows) | Small to Medium |