Common Table Expressions for Readability

The query works, but its nested subqueries are hard to review. Common table expressions for readability let you name each step without claiming it becomes stored data.

Write Common Table Expressions for Readability, One Step at a Time

A CTE is a named query expression used by the statement that follows it. It can make a complicated transformation easier to read by separating filtering, grouping, and ranking into visible steps. The name should say what the step represents, not merely cte1.

I begin by writing the output grain beside each step. A filtered order set can still be one row per order. A grouped customer set is one row per customer. Naming those stages makes accidental row multiplication easier to spot.

Ask whether a CTE makes the query clearer than a derived table. For a short single-use expression, either can be fine. Use the form that helps a reviewer follow the business rule. Common table expressions for readability give a real maintenance benefit when the logic changes next month.

WITH PaidOrders AS
(
    SELECT OrderId, CustomerId, OrderAmount
    FROM dbo.Orders
    WHERE OrderStatus = 'Paid'
)
SELECT CustomerId, SUM(OrderAmount) AS PaidTotal
FROM PaidOrders
GROUP BY CustomerId;

Build Common Table Expressions for Readability in Named Stages

A second CTE can consume the first. That lets you show the reasoning in order: filter eligible rows, aggregate by customer, then keep customers above a threshold. The final SELECT tells the reader what is returned. Avoid adding a stage for every column expression. Too many names can obscure the path.

I check the row count at each stage during development by selecting from one CTE at a time. The finished statement can include all stages, but a targeted check catches a join that changes grain unexpectedly. Keep the predicates in the correct stage.

The CTE syntax needs a semicolon before WITH if another statement precedes it in the same batch. A leading semicolon is a common defensive style. SQL Server then parses the intended statement boundary clearly.

;WITH PaidOrders AS
(
    SELECT CustomerId, OrderAmount
    FROM dbo.Orders
    WHERE OrderStatus = 'Paid'
),
CustomerTotals AS
(
    SELECT CustomerId, SUM(OrderAmount) AS PaidTotal
    FROM PaidOrders
    GROUP BY CustomerId
)
SELECT CustomerId, PaidTotal
FROM CustomerTotals
WHERE PaidTotal >= 1000;

Do Not Assume a CTE Is Cached

A CTE is not an automatic temporary table. Its results are not materialized just because you gave the expression a name. The optimizer works with the full statement and can choose different physical strategies. If you reference the same CTE more than once, check the plan and work rather than assuming one execution.

For expensive reusable intermediate results, a temporary table can be better. It provides a materialized boundary and can have indexes and statistics. That adds writes to tempdb and another step, so choose it from measured workload rather than habit.

I look at actual plans when a long CTE chain is slow. The problem can be poor cardinality estimates, repeated work, or a missing index on a base table. Renaming the CTE will not change any of those. The name helps the human find the problem.

SELECT name, create_date
FROM tempdb.sys.tables
WHERE name LIKE N'#CustomerTotals%';

Use Recursion for Hierarchies

A recursive CTE has an anchor query and a recursive member joined with UNION ALL. It can walk a parent-child hierarchy, such as departments. Start from a known root, join children to the current level, and return the accumulated rows.

I test the anchor and one level before running the full traversal. A bad join can repeat the same row forever. MAXRECURSION limits the depth of one statement and helps catch a cycle, but it is not a replacement for clean hierarchy constraints.

The following example starts from a chosen department. It assumes DepartmentId is unique and ParentDepartmentId points to its parent. The level is for display and diagnostics. A production query should define what happens if the hierarchy contains a cycle.

DECLARE @RootId int = 1;
WITH Tree AS
(
    SELECT DepartmentId, ParentDepartmentId, 0 AS LevelNumber
    FROM dbo.Department
    WHERE DepartmentId = @RootId
    UNION ALL
    SELECT d.DepartmentId, d.ParentDepartmentId, t.LevelNumber + 1
    FROM dbo.Department AS d
    JOIN Tree AS t ON d.ParentDepartmentId = t.DepartmentId
)
SELECT DepartmentId, ParentDepartmentId, LevelNumber
FROM Tree
OPTION (MAXRECURSION 100);

Handle Cycles and Depth Deliberately

A parent row that eventually points back to itself creates a cycle. A recursion limit stops the query with an error, which is better than unbounded work. For a system that must report bad hierarchies, add a path or visited-key method and mark the cycle. Then correct the source data.

Deep legitimate hierarchies need a limit chosen from the domain, not an arbitrary large number. Setting MAXRECURSION 0 removes the limit and should be used only when cycle prevention is established. A hierarchy used for a company org chart has different expectations from a graph of arbitrary relationships.

I keep a query that finds orphaned parent IDs. Recursion from a root cannot show rows it never reaches. Missing parents can disappear from the result quietly. Validate the whole table as well as the traversal.

SELECT d.DepartmentId, d.ParentDepartmentId
FROM dbo.Department AS d
LEFT JOIN dbo.Department AS p
  ON p.DepartmentId = d.ParentDepartmentId
WHERE d.ParentDepartmentId IS NOT NULL
  AND p.DepartmentId IS NULL;

Watch Predicate Placement

A filter inside a CTE changes the rows available to later stages. A filter outside can change only the final result. For window functions, this distinction matters. Filtering to one month before calculating a rolling average gives a short first window. Filtering after calculation can preserve earlier rows needed for context.

I write the business question at the top of the query. Then I decide which stage owns each predicate. A CTE chain can make the choice visible, but it can also hide a misplaced filter behind a good name. Read every stage in order.

Check whether ORDER BY is needed only for the final result or inside a window function. A CTE does not preserve presentation order merely because one step sorted data. The final SELECT needs its own ORDER BY when readers expect order.

Balance Common Table Expressions for Readability With Measured Plans

Use names such as EligibleOrders and CustomerTotals. Avoid names that assert a performance property, such as CachedOrders, when no materialization is guaranteed. Keep column aliases consistent so the grain and units are clear across steps.

I compare the plan of a readable CTE query with a temporary table design only when performance justifies the extra complexity. The optimizer can handle many CTEs well. It can also repeat expensive work under certain shapes. The actual plan tells you what happened on your server.

CTEs are valuable because they turn a long statement into named logical relations. Use them to expose reasoning, test each stage, and keep recursion bounded. Let measured work, not the presence of WITH, decide whether another storage step is needed.

A common table expression does not automatically cache its result. If the same expensive expression is referenced several times, inspect the plan and consider whether a temporary table makes the work clearer and cheaper. Which step is intended to filter rows, and which step merely gives a name to a calculation? I keep each CTE focused so a reader can follow the transformation without jumping through nested subqueries.

Related reading on this blog: Replacing a Cursor with a Common Table Expression and Making Recursive Parent-Child Queries Efficient.

A CTE is not a cache, it is a name for a logical step in one statement.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.