SQL Server / Intermediate / Lesson 10

CTEs, windows, and temporary results

Express multistage analysis without confusing a named query with stored data. Rank rows deterministically and calculate running totals with an explicit window frame.

About 4 min readContent reviewed 2026-10-23

On this page

Lesson goals

Explain CTE scope and materialization limits.
Use ROW_NUMBER for per-group selection.
Choose window frames and temporary storage deliberately.

Use CTEs to expose reasoning

A common table expression gives a query expression a name for one following statement. It improves readability when filtering, grouping, and ranking need separate logical stages. It is not a persistent view or a guaranteed materialized cache. Referencing a CTE several times can cause repeated work; inspect the resulting plan rather than assuming the name implies stored rows.

Terminate the preceding statement with a semicolon; the familiar ;WITH spelling makes that boundary explicit. Several CTE definitions can share one WITH clause separated by commas. Later definitions can reference earlier ones, but ordinary forward references are not supported. Choose stage names that describe their grain, such as CustomerTotals, instead of generic numbered placeholders.

Choose one row per group deterministically

ROW_NUMBER assigns sequential values within each partition using its window ORDER BY. The example finds the latest order per customer. OrderId breaks equal timestamps, so customer 1 selects order 11. Filtering rn requires an outer query or CTE because the window result is not available to the same query block's WHERE.

ROW_NUMBER, RANK, and DENSE_RANK express different tie policies. ROW_NUMBER always assigns distinct sequence numbers; RANK gives tied rows equal ranks and leaves gaps; DENSE_RANK leaves no ranking gaps. A ranking order determines calculation, while the final ORDER BY determines presentation. Include both when the displayed output itself must have a stable order.

sql Latest order with an explicit tie rule

;WITH Orders AS (
 SELECT * FROM (VALUES (10,1,'20260101'),(11,1,'20260101'),
 (12,2,'20260102'))v(OrderId,CustomerId,CreatedOn)
), Ranked AS (
 SELECT *,ROW_NUMBER() OVER
 (PARTITION BY CustomerId ORDER BY CreatedOn DESC,OrderId DESC) AS rn
 FROM Orders
)
SELECT CustomerId,OrderId FROM Ranked WHERE rn=1 ORDER BY CustomerId;

Expected output

CustomerId OrderId
1          11
2          12

The synthetic date strings have the same fixed compact format, so their ordering matches chronological ordering here. Real schema columns should use date/time types.

Define the running-total frame

A window aggregate retains detail rows while calculating across related rows. SUM(Amount) OVER (...) differs from GROUP BY because it does not collapse the result. Specify PARTITION BY for independent running totals, ORDER BY for sequence, and ROWS UNBOUNDED PRECEDING for a row-by-row accumulation from the partition start.

The example uses a unique sequence and yields 10, 30, and 35. With nonunique ordering, default RANGE framing can include all peers at once, producing jumps that surprise learners expecting individual steps. An explicit ROWS frame and a unique tiebreaker make the calculation contract visible. Neither window ordering nor frame choice replaces the final output ordering.

sql An explicit row-based running total

SELECT Seq,Amount,
 SUM(Amount) OVER (ORDER BY Seq ROWS UNBOUNDED PRECEDING) AS running_total
FROM (VALUES (1,10),(2,20),(3,5))v(Seq,Amount)
ORDER BY Seq;

Expected output

Seq Amount running_total
1   10     10
2   20     30
3   5      35

Each row adds its amount to all earlier rows in the defined sequence.

Choose when to store intermediate rows

A local #temporary table stores intermediate rows in tempdb and can support indexes and statistics. A table variable has a different scope and optimizer behavior. Neither is automatically an in-memory object immune to resource costs. Use temporary storage when reuse, staged transformations, or estimation benefits justify its writes and cleanup.

SQL Server 2019 with compatibility level 150 introduced table-variable deferred compilation, which can improve an initial cardinality estimate. It does not give table variables every property of temporary tables or guarantee good plans for later differently sized executions. State the engine and compatibility assumptions, then compare representative workloads rather than relying on outdated fixed-row-estimate folklore.

Bound recursion and verify grain

A recursive CTE combines an anchor and a recursive member to traverse structures such as a hierarchy. Design a termination condition and consider malformed cycles. MAXRECURSION can bound work for a query but does not correct a cyclic data model. Treat hierarchy depth and duplicate paths as explicit requirements before attempting a large traversal.

For every multistage query, write the grain after each stage and verify a tiny data set with ties, missing groups, and repeated references. A cleaner query can still be semantically wrong or more expensive. Separate the readability improvement from claims about materialization or performance, and use plans only after confirming expected rows.

Common errors and fixes

Invalid object name for a CTE.

Why it happens: A second statement tried to reuse its expired name.
How to fix it: Use another CTE definition or store reusable rows in a temporary table.
Prevent it next time: Document one-statement CTE scope.

Latest-row selection changes on ties.

Why it happens: The window ordering is not unique.
How to fix it: Add a business-approved unique tiebreaker.
Prevent it next time: Include tied timestamps in examples.

Running totals jump across equal values.

Why it happens: The frame groups peers under RANGE semantics.
How to fix it: Specify the intended ROWS frame and ordering.
Prevent it next time: Review frame semantics explicitly.

Check your understanding

1. For scores 90, 90, and 80, compare RANK and DENSE_RANK descending.

Show worked solution

RANK yields 1,1,3 while DENSE_RANK yields 1,1,2. ROW_NUMBER yields three distinct positions but needs a tie rule to choose which 90 gets position 1.

2. A costly intermediate result is used by four later statements. Is one CTE enough?