.svg)
.svg)
.svg)
.svg)
.svg)
.svg)
.svg)
Updated and tested 12 October 2026
A common table expression names a query so the statement underneath can treat it like a table. Search traffic calls it an MSSQL CTE and the documentation calls it a CTE. Same thing. Three levels of nested subquery turn into a list of named steps that read top to bottom, which is most of why I write them. What the name does not buy you is a stored result. SQL Server does not compute the query once and hold on to it, and the reference page says so in plain English, in a sentence I quote in full further down.
Everything below runs against four tables I reuse across this batch: dbo.customers, dbo.orders, dbo.order_items and dbo.employees. Standard orders schema. The employee table carries a self-referencing manager_id, which the hierarchy examples need.
WITH clause syntax and the column alias form
The WITH clause in SQL Server takes a name, the keyword AS, a parenthesised query and then the single statement that consumes it.
WITH recent_orders AS
(
SELECT order_id, customer_id, order_date, total_amount
FROM dbo.orders
WHERE order_date >= '2026-01-01'
)
SELECT c.company_name,
COUNT(*) AS order_count,
SUM(r.total_amount) AS revenue
FROM recent_orders AS r
JOIN dbo.customers AS c ON c.customer_id = r.customer_id
GROUP BY c.company_name
ORDER BY revenue DESC;Filtering inside a CTE is just filtering, and WHERE clause behaviour in SQL Server is identical on both sides of the closing bracket. Scope is what differs. The consuming statement has to follow immediately, with no DECLARE between them, no SET and no second query. The name exists for one statement and is then gone, so a CTE never needs cleaning up.
The CTE can declare its own output column names, which saves aliasing them inside:
WITH recent_orders (order_id, customer_id, placed_on, amount) AS
(
SELECT order_id, customer_id, order_date, total_amount
FROM dbo.orders
WHERE order_date >= '2026-01-01'
)
SELECT placed_on, amount
FROM recent_orders
WHERE amount > 500;Name every column or none of them, and match the count exactly, or you get an error about the number of columns not matching. I skip this form on simple CTEs and use it on recursive ones, where naming the columns once at the top saves aliasing the same expression in two separate branches.
The semicolon before WITH and the ;WITH idiom
Microsoft's wording on the WITH common table expression reference is short: "When a CTE is used in a statement that is part of a batch, the statement before it must be followed by a semicolon."
DECLARE @cutoff date = '2026-01-01';
WITH recent_orders AS
(
SELECT order_id, order_date
FROM dbo.orders
WHERE order_date >= @cutoff
)
SELECT COUNT(*) AS orders_since_cutoff
FROM recent_orders;Drop the semicolon after that DECLARE and the parser hands you a message that explains itself unusually well for T-SQL:
Incorrect syntax near the keyword 'with'. If this statement is a common
table expression, an xmlnamespaces clause or a change tracking context
clause, the previous statement must be terminated with a semicolon.WITH is badly overloaded in T-SQL. It introduces table hints in FROM dbo.orders WITH (NOLOCK), options in CREATE VIEW ... WITH SCHEMABINDING, result set definitions in EXECUTE ... WITH RESULT SETS and a handful of others. With no terminator on the statement above, the parser cannot tell which one it is looking at. That error text is also one of the very few in T-SQL that names the fix. The rest hand you a line number and the word "near". I have always assumed somebody on the parser team got tired of the same support ticket. Anyway.
That is where ;WITH comes from. Somebody got bitten once, started writing the semicolon on the front of the CTE where it cannot be forgotten, and the habit spread through every code base I have inherited. It works. I still do not write it, because a leading semicolon on line one is a workaround for not terminating the line above, and terminating the line above is free. Snippets are the exception. Generated code, and anything a support engineer will paste into an unknown batch in a hurry, gets the leading semicolon, since there the statement above yours is not yours to fix.
Common table expression results are not materialized
This is the sentence that changes how you write queries, straight from the same reference page: "Query results from common table expressions aren't materialized. Each outer reference to the named result set requires the defined query to be re-executed. For queries that require multiple references to the named result set, consider using a temporary object instead."
Read it again with a plan in mind. A CTE is closer to a macro than a table. The optimiser inlines the definition at every point of reference and then optimises the whole expanded tree. Reference it three times and the work inside runs three times.
WITH monthly AS
(
SELECT DATEFROMPARTS(YEAR(order_date), MONTH(order_date), 1) AS month_start,
SUM(total_amount) AS revenue
FROM dbo.orders
WHERE status = 'shipped'
GROUP BY DATEFROMPARTS(YEAR(order_date), MONTH(order_date), 1)
)
SELECT curr.month_start,
curr.revenue,
prev.revenue AS prev_month,
yr.revenue AS same_month_last_year
FROM monthly AS curr
LEFT JOIN monthly AS prev ON prev.month_start = DATEADD(month, -1, curr.month_start)
LEFT JOIN monthly AS yr ON yr.month_start = DATEADD(year, -1, curr.month_start)
ORDER BY curr.month_start;Turn on the actual execution plan and count the operators. Three scans of dbo.orders, three hash aggregates, three of everything. The query reads like one aggregation feeding three uses. It executes as three aggregations. On a table with a few thousand rows nobody notices. On an orders table with eighty million rows and a filtered index that still has to be scanned, you have tripled the slowest thing in the report.
Rewriting the multi-reference CTE as a temp table
SELECT DATEFROMPARTS(YEAR(order_date), MONTH(order_date), 1) AS month_start,
SUM(total_amount) AS revenue
INTO #monthly
FROM dbo.orders
WHERE status = 'shipped'
GROUP BY DATEFROMPARTS(YEAR(order_date), MONTH(order_date), 1);
CREATE CLUSTERED INDEX ix_monthly_month ON #monthly (month_start);
SELECT curr.month_start,
curr.revenue,
prev.revenue AS prev_month,
yr.revenue AS same_month_last_year
FROM #monthly AS curr
LEFT JOIN #monthly AS prev ON prev.month_start = DATEADD(month, -1, curr.month_start)
LEFT JOIN #monthly AS yr ON yr.month_start = DATEADD(year, -1, curr.month_start)
ORDER BY curr.month_start;
DROP TABLE #monthly;One aggregation. Twelve or twenty-four rows land in tempdb, the clustered index makes the self joins trivial, and the optimiser now has real statistics on the intermediate set. Before that it was working from an estimate derived from a guess. That clustered index is doing more work than people expect, and on a result set this small it costs nothing.
I switch when two conditions hold at once: the CTE is referenced more than once and its body does real work. Real work means an aggregation, a scan of a large table, a join across several tables or a scalar function call per row. A CTE referenced twice that filters a 400-row lookup table by one column can stay a CTE forever. Two references to a nine-table join that reads a quarter of your fact table goes into tempdb the same afternoon you find it.
