skip to content

When would you stage an intermediate result in a temporary table instead of leaving it as a common table expression, a derived table, or a table variable?

level: middleimportance: must knowfreq 46%

answer

  1. materialize for: reuse, statistics, indexability
  2. inline keeps pushdown + join reordering
  3. CTE fencing is engine/version dependent
  4. table variable = 1-row estimate historically
  5. high-frequency OLTP: catalog churn dominates

basics

~20 s

Stage in a temporary table when the intermediate is read more than once, when the optimizer needs real statistics on it, or when it should be indexed. Otherwise keep it inline so the optimizer can see and reshape the whole query. Table variables sit in between: real storage, but historically poor cardinality estimates.

solid answer

~50 s

Three reasons justify materializing into a temporary table: 1. **Reuse** — the intermediate is consumed several times. Inline constructs may be re-evaluated per reference; a temp table computes once. 2. **Statistics** — after populating and analyzing, the planner has real row counts and histograms for the joins that follow. Estimating through a deep inline expression is where big plans usually go wrong. 3. **Indexability** — you can index or cluster the intermediate. No inline construct allows that. Against those: an inline expression stays inside one optimization unit, so predicates push down, joins reorder across the boundary, and there are no extra writes, no catalog churn, no temporary-space use. **Table variables** (SQL Server) look like a lightweight middle ground but historically carried a fixed one-row estimate, which produced the same catastrophic nested-loop plans as a bad function estimate; deferred compilation in SQL Server 2019 improved but did not eliminate this. Use them for genuinely tiny sets. Default to inline; materialize when one of the three reasons actually applies.

code

sql · 11 lines
sql
CREATE TEMP TABLE active_customers ON COMMIT DROP AS
SELECT customer_id, sum(total) AS spend
FROM orders
WHERE created_at >= current_date - 30
GROUP BY customer_id;

CREATE INDEX ON active_customers (customer_id);
ANALYZE active_customers;

SELECT * FROM active_customers a JOIN support_ticket t USING (customer_id);
SELECT * FROM active_customers a JOIN campaign_target c USING (customer_id);

go deeper

for a junior

Know that a temp table stores intermediate rows for reuse while a CTE or subquery is part of the query, and that reuse is the simplest reason to materialize.

for a middle

Give the three reasons to materialize — reuse, statistics, indexability — and the cost: lost pushdown and join reordering, plus writes.

for a senior

Diagnose from the plan: identify where estimation goes wrong and split there, gather statistics, index the intermediate, and weigh catalog churn on hot paths.

for a principal

Frame it as where the optimization boundary belongs in a pipeline, note that CTE fencing semantics vary by engine and version so portable code cannot rely on them, and set team conventions accordingly.

## The real question: one optimization unit, or two? Every option here is a way to name an intermediate result. What differs is whether the intermediate stays inside the optimizer's view of a single statement or becomes an independent, executed step. **Derived tables and CTEs** are usually part of the same statement. The optimizer expands them, pushes predicates into them, reorders joins across their boundary, and may evaluate them more than once or not at all. Nothing is written to storage; nothing is left behind. **Temporary tables** are a hard boundary. Step one runs to completion and writes rows; step two is planned separately against a real object with real statistics. That is the entire tradeoff, and every consideration below follows from it. ## When the boundary helps **Reuse.** If four later queries read the same intermediate, materializing computes it once. Referencing a non-materialized CTE four times can mean four evaluations. **Statistics.** This is the underrated one. Planning a five-level nested expression means estimating selectivity through filters, joins, and aggregates, and errors compound multiplicatively. Materialize, gather statistics, and the next step starts from measured truth. When a long query is slow because of a bad plan rather than raw volume, splitting it at the point where estimates go wrong is often the largest single improvement available. **Indexability.** A temp table can carry an index or a clustering order chosen for the joins that follow. For a large intermediate joined repeatedly, that can be decisive. **Complexity control.** A ten-CTE query that nobody can modify safely is a real cost. Splitting it into named staged steps inside a procedure is sometimes justified on maintainability alone — but say so explicitly rather than pretending it is a performance argument. ## When the boundary hurts **Lost pushdown.** Filters in the outer query cannot reach into an already-materialized table; you have to remember to apply them while populating it. Forget, and you materialize ten million rows to use ten thousand. **No cross-boundary reordering.** The optimizer can no longer choose a global join order; you have hard-coded one. **Write cost and shared space.** Rows are written, possibly spilling to shared temporary space that every session's sorts and hashes also use. **Catalog churn.** Creating temp tables at high frequency contends on system catalogs — a well-documented bottleneck under high-concurrency OLTP. **No pipelining.** Step two cannot begin until step one finishes, whereas an inline plan streams. ## CTE materialization is engine-dependent A crucial subtlety: whether a CTE behaves as an optimization fence differs by engine and version. PostgreSQL treated CTEs as an unconditional fence before version 12 and since then inlines them when they are referenced once and side-effect-free, with `MATERIALIZED` / `NOT MATERIALIZED` to force either behaviour. SQL Server and Oracle generally inline CTEs, with hints available to force materialization. Recursive CTEs and CTEs containing data-modifying statements are materialized regardless. So "use a CTE for readability" is safe advice, but "a CTE materializes" is only true on some engines and versions — check before relying on it for performance. ## Table variables SQL Server's table variables are real storage in `tempdb` with different semantics: minimal logging of metadata, no rollback of their contents in some paths, and — historically — a fixed estimate of one row regardless of contents, because statistics were not maintained on them. That estimate produced exactly the pathology of a bad function cardinality: nested loops chosen for one row, executed a million times. SQL Server 2019's deferred compilation defers the estimate until first use, which helps materially but still does not give them column statistics. Practical rule: table variables for small, known-tiny sets or where you need contents to survive a rollback; temporary tables when the set is large or the plan needs statistics. ## A decision procedure 1. Will the intermediate be read more than once? → lean temp table. 2. Are the plans downstream bad because of estimation error at this point? → temp table, and gather statistics after populating. 3. Does a downstream join want an index on the intermediate? → temp table. 4. Is this a high-frequency OLTP path called thousands of times a second? → prefer inline, catalog churn will dominate. 5. Otherwise → keep it inline and let the optimizer work with the whole statement. Saying "temp tables are faster" or "CTEs are faster" as a blanket rule is the wrong answer; the right answer names the conditions.

  • Why can splitting one large query into a staged temporary table dramatically improve the plan even when the total work is identical?
    Because estimation errors compound through a deep expression tree: a mis-estimate three levels down is multiplied by every operator above it, and the join order and join methods chosen from that number can be wrong by orders of magnitude. Materializing at the point where the estimate goes wrong and gathering statistics replaces a guess with a measurement for everything downstream. The extra writes are usually far cheaper than the bad plan they eliminate.
  • What do you lose by materializing an intermediate that you keep when it stays inline?
    Predicate pushdown from the outer query into the intermediate, cross-boundary join reordering, and pipelining — the second step cannot start until the first finishes. You also add write cost, temporary space usage, and catalog work for creating the object. In a high-frequency OLTP path those costs can exceed any planning benefit, which is why inline should be the default.
  • Is a common table expression guaranteed to be evaluated only once?
    No, and that is engine- and version-dependent. PostgreSQL before version 12 always materialized CTEs, so once was guaranteed; from version 12 it inlines a CTE referenced a single time unless it is marked MATERIALIZED, and other engines generally inline by default. Recursive CTEs and CTEs containing data-modifying statements are exceptions that are materialized regardless.

An inline expression is a step in one recipe the chef can reorder; a temp table is a prepped tray set aside — reusable and measurable, but the sequence is now fixed.

saying these in an interview costs you the question

  • "CTEs always materialize" — true only on some engines and versions.
  • "Temp tables are always faster than CTEs", or the reverse, as a blanket rule.
  • Materializing without gathering statistics afterwards, throwing away the main benefit.
  • Assuming a table variable behaves like a temp table for the optimizer.
  • Forgetting that filters from the outer query no longer reach a materialized intermediate.
  • Introducing temp tables into a high-frequency OLTP path without considering catalog contention.

context

resolving links…