SQL for .NET developers Intermediate

Common table expressions (CTEs)

A CTE gives a query a name with WITH, so you can build a complex query in readable steps. This lesson covers normal and recursive CTEs, the classic remove-duplicates trick, and when a temp table is the better choice for very large data.

How a query with a CTE runs
  1. StartWITH defines a named queryWITH MonthlySales AS (SELECT ...) describes a result; nothing runs yet
  2. Step 2More CTEs can followSeparate them with commas; each one can read the CTEs above it
  3. Step 3The main statement uses themSELECT, INSERT, UPDATE or DELETE reads the CTE like a table
  4. Step 4SQL Server builds one planThe CTEs are folded into the main query and optimized together
  5. EndThe result is returnedThe CTE disappears when the statement ends

A CTE is a name for a query, not a stored table. That is why it helps readability but does not cache results.

The idea in plain words

A common table expression is a named, temporary result that exists only for one statement. You write WITH Name AS (a query), and the next SELECT, INSERT, UPDATE or DELETE can read Name as if it were a table.

CTEs do not make a query faster on their own. Their job is readability: instead of nesting subqueries three levels deep, you write the query top to bottom as a series of steps, each with a clear name.

Example: best-selling product per month

Two CTEs are chained with a comma. The first totals sales per product per month, the second ranks them, and the final SELECT keeps the winner of each month.

SQL
WITH MonthlySales AS (
    SELECT p.ProductId, p.Name,
           DATEFROMPARTS(YEAR(o.OrderedAt), MONTH(o.OrderedAt), 1) AS Month,
           SUM(ol.Quantity * ol.UnitPrice) AS Revenue
    FROM OrderLines ol
    JOIN Orders o   ON o.OrderId = ol.OrderId
    JOIN Products p ON p.ProductId = ol.ProductId
    WHERE o.OrderedAt >= '2026-01-01'
    GROUP BY p.ProductId, p.Name,
             DATEFROMPARTS(YEAR(o.OrderedAt), MONTH(o.OrderedAt), 1)
),
Ranked AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY Month ORDER BY Revenue DESC) AS Rn
    FROM MonthlySales
)
SELECT Month, Name, Revenue
FROM Ranked
WHERE Rn = 1
ORDER BY Month;

Recursive CTEs: trees and hierarchies

A recursive CTE has two parts joined by UNION ALL: an anchor query that returns the starting rows, and a recursive query that joins back to the CTE itself. SQL Server repeats the recursive part until it returns no new rows. It is the standard way to walk a category tree or an org chart stored as ParentId columns.

SQL
WITH CategoryTree AS (
    -- anchor: top-level categories
    SELECT CategoryId, Name, ParentId, 0 AS Depth
    FROM Categories
    WHERE ParentId IS NULL

    UNION ALL

    -- recursive part: children of the rows found so far
    SELECT c.CategoryId, c.Name, c.ParentId, t.Depth + 1
    FROM Categories c
    JOIN CategoryTree t ON c.ParentId = t.CategoryId
)
SELECT REPLICATE('  ', Depth) + Name AS Category
FROM CategoryTree
ORDER BY Depth, Name
OPTION (MAXRECURSION 100);   -- stop runaway loops (default is 100, 0 = no limit)

Removing duplicate rows

A favourite interview task. Number the rows inside each group of duplicates, then delete every row numbered above 1. Deleting through the CTE deletes from the underlying table.

SQL
WITH Dupes AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY Email ORDER BY CustomerId) AS Rn
    FROM Customers
)
DELETE FROM Dupes WHERE Rn > 1;   -- keeps the oldest row per email

CTEs on huge tables

SQL Server does not store a CTE's result. It folds the CTE into the main query, so referencing the same CTE twice runs its query twice. On a table with millions of rows that can double the work.

  • Reading a CTE once: fine, it costs the same as the equivalent subquery.
  • Reading the same expensive step several times: copy it into a temp table (SELECT ... INTO #Monthly) and add an index; the work is done once.
  • Filter early: put WHERE conditions inside the first CTE so later steps handle fewer rows.
  • Make sure joins and filters in the CTE can use indexes, exactly as in any other query; check the actual execution plan.

Common mistakes

  • Forgetting that the statement before WITH must end with a semicolon, which causes Incorrect syntax near WITH.
  • Putting ORDER BY inside a CTE. It is not allowed without TOP, and it would not order the final result anyway.
  • A recursive CTE with no stopping condition, which fails at the MAXRECURSION limit.
  • Expecting a CTE to be reusable in the next statement. It exists for one statement only; use a view for that.

Interview question

What is the difference between a CTE and a temp table?

A CTE is a named query that lives for one statement and is not stored; SQL Server expands it into the main query, so it costs the same as a subquery and runs again each time it is referenced. A temp table (#Name) actually stores rows in tempdb, lasts for the session, can have indexes and statistics, and can be used by several statements. Use a CTE for readability and recursion, and a temp table when a large intermediate result is reused or needs an index.

Your progress is saved only in this browser.

Want the full details? Read the official Microsoft documentation for this topic.