SQL Server Common Table Expressions with Examples

A common table expression (CTE) is a named result set that exists for one statement — think of it as a readable, reusable subquery you define up front with WITH. CTEs turn nested-subquery spaghetti into a pipeline of named steps, and their recursive form is the standard way to walk hierarchies in SQL Server. Every example below ran against the demo database in the companion repo; results shown are real.

The shape

WITH CteName AS (
    SELECT ...   -- any query
)
SELECT ... FROM CteName ...;   -- must immediately follow

The CTE lives only for the statement attached to it. It's not stored, not indexed, and not materialized by default — SQL Server usually inlines it into the outer query, so a CTE is about readability, not caching. (If you reference an expensive CTE three times, it may be evaluated three times — a temp table is the tool when you truly want to compute once; see temp table vs table variable.)

Replacing a nested subquery

Monthly totals per customer, then joined to names:

WITH MonthlyTotals AS (
    SELECT CustomerId,
           CONVERT(char(7), OrderDate, 126) AS Month,   -- '2026-06'
           SUM(Amount) AS Total
    FROM Orders
    GROUP BY CustomerId, CONVERT(char(7), OrderDate, 126)
)
SELECT c.Name, m.Month, m.Total
FROM MonthlyTotals m
JOIN Customers c ON c.CustomerId = m.CustomerId
ORDER BY m.Month, c.Name;

Name   Month    Total
-----  -------  -------
Asha   2026-06  1650.00
Meera  2026-06   780.00
Rahul  2026-06  2300.00
Meera  2026-07  1310.00

The aggregation step has a name, the join reads like a sentence, and each part can be tested by selecting from the CTE alone. That's the whole pitch.

CTEs + window functions: the top-N-per-group idiom

The single most-used CTE pattern in real codebases — rank inside the CTE, filter outside (you can't put a window function in WHERE directly):

WITH Ranked AS (
    SELECT Name, Department, Salary,
           ROW_NUMBER() OVER (PARTITION BY Department ORDER BY Salary DESC) AS rn
    FROM Employees
)
SELECT Department, Name, Salary
FROM Ranked
WHERE rn = 1
ORDER BY Department;

Department   Name   Salary
-----------  -----  ---------
Engineering  Amit   140000.00
Leadership   Priya  250000.00
Sales        Neha   120000.00

Top earner per department in six lines. Change rn = 1 to rn <= 3 for top three; swap ROW_NUMBER for RANK if ties should share a position — the differences are covered in generating sequence numbers.

Chaining CTEs

Multiple CTEs separate steps of a derivation; later ones can reference earlier ones:

WITH OrderTotals AS (
    SELECT CustomerId, SUM(Amount) AS Lifetime
    FROM Orders GROUP BY CustomerId
),
Tiers AS (
    SELECT CustomerId, Lifetime,
           CASE WHEN Lifetime >= 2000 THEN 'Gold'
                WHEN Lifetime >= 1000 THEN 'Silver'
                ELSE 'Bronze' END AS Tier
    FROM OrderTotals
)
SELECT c.Name, t.Lifetime, t.Tier
FROM Tiers t JOIN Customers c ON c.CustomerId = t.CustomerId;

One WITH, comma-separated definitions — a readable pipeline where each intermediate result has a name.

Recursive CTEs — walking hierarchies

The self-join answer to "who reports to whom" only reaches one level. A recursive CTE walks the whole tree, any depth:

WITH OrgChart AS (
    -- anchor: the root(s)
    SELECT EmployeeId, Name, ManagerId, 0 AS Level
    FROM Employees
    WHERE ManagerId IS NULL

    UNION ALL

    -- recursive member: joins back to the CTE itself
    SELECT e.EmployeeId, e.Name, e.ManagerId, oc.Level + 1
    FROM Employees e
    JOIN OrgChart oc ON e.ManagerId = oc.EmployeeId
)
SELECT REPLICATE('   ', Level) + Name AS Hierarchy, Level
FROM OrgChart
ORDER BY Level, Name;

Hierarchy      Level
------------   -----
Priya          0
   Amit        1
   Neha        1
      Divya    2
      Rohan    2
      Tara     2
      Vikram   2

How it executes: the anchor runs once (the CEO), then the recursive member runs repeatedly — each pass joining the previous pass's rows to find their reports — until a pass returns nothing. Level + 1 tracks depth for free.

Two safety notes:

• The default recursion limit is 100 levels; a cyclic hierarchy (A manages B manages A) hits it and errors. Raise or remove it with OPTION (MAXRECURSION 0) — but only when you've ruled out cycles.
• Recursive CTEs also handle category trees, bill-of-materials explosions, and path-building (concatenate names down the tree) — anywhere a table references itself.

CTEs you can write to

A CTE can be the target of UPDATE or DELETE — the classic dedup:

WITH Numbered AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY Sku ORDER BY ProductId) AS rn
    FROM Products
)
DELETE FROM Numbered WHERE rn > 1;   -- keeps the first row per Sku

Deleting from the CTE deletes the underlying rows — surgical duplicate removal with no temp table.

CTE, subquery, view, or temp table?

Reach for the CTE first; graduate to a temp table when profiling says the same CTE is being re-evaluated painfully, and to a view when other queries want the same abstraction.