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.
- StartWITH defines a named queryWITH MonthlySales AS (SELECT ...) describes a result; nothing runs yet
- Step 2More CTEs can followSeparate them with commas; each one can read the CTEs above it
- Step 3The main statement uses themSELECT, INSERT, UPDATE or DELETE reads the CTE like a table
- Step 4SQL Server builds one planThe CTEs are folded into the main query and optimized together
- 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.
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.
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.
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 emailCTEs 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.
Want the full details? Read the official Microsoft documentation for this topic.