Skip to content
Tutorials Center
My learning

Topics

Lesson 17 of 39 ยท Joins, CTEs and Window Functions

CTEs and Recursive Queries in SQL Server

Write readable queries with common table expressions (WITH), chain several CTEs, update through a CTE and walk hierarchies with recursive CTEs, MAXRECURSION and cycle protection.

A common table expression (CTE) is a named, temporary result set defined at the top of a statement with WITH. It exists only for that one statement. CTEs turn a tangle of nested subqueries into a top-to-bottom story: first compute this, then that, then combine. A special kind, the recursive CTE, can walk through trees and graphs: an organization chart, folders inside folders, a bill of materials.

Your first CTE

SQL
WITH order_totals AS (
    SELECT o.customer_id,
           SUM(i.quantity * i.unit_price) AS total_spent
    FROM dbo.orders AS o
    JOIN dbo.order_items AS i ON i.order_id = o.id
    GROUP BY o.customer_id
)
SELECT c.first_name, c.last_name, t.total_spent
FROM dbo.customers AS c
JOIN order_totals AS t ON t.customer_id = c.id
WHERE t.total_spent > 400
ORDER BY t.total_spent DESC;

The part after WITH name AS (...) is the CTE; the statement that follows can use it like a table or view. A CTE does not store data and is not guaranteed to be computed once: it is a query definition that the optimizer expands into the main statement, like a view that lives for one statement. If you need to reuse an expensive result many times, a temporary table may be faster.

SQL A first CTE ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

T-SQL syntax details: the statement before a WITH must end with a semicolon (a habit that earned the joke ";WITH"), and you can specify column names in parentheses: WITH totals (customer_id, total_spent) AS (...).

Chaining CTEs

You can define several CTEs separated by commas; each can use those defined before it. This lets you build an analysis in small named steps:

SQL Two CTEs in a chain ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Compared with a derived table, a CTE is easier to read, and can be referenced more than once in the main query (for example in a self join).

Data modification through a CTE

In T-SQL, UPDATE, DELETE and INSERT can target a CTE, and the change is applied to the underlying table. This is the neat way to delete duplicates:

SQL
WITH numbered AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
    FROM dbo.subscribers
)
DELETE FROM numbered WHERE rn > 1;     -- keeps the first row of each email

Recursive CTEs

A recursive CTE has two parts joined by UNION ALL:

  1. The anchor member: a query that does not refer to the CTE; it provides the starting rows.
  2. The recursive member: a query that refers to the CTE itself, and produces the next level from the previous one.

SQL Server runs the anchor, then repeatedly feeds the newest rows into the recursive member until it returns nothing.

SQL
WITH chain AS (
    SELECT id, first_name, manager_id, 0 AS depth          -- anchor: the top boss
    FROM dbo.employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.first_name, e.manager_id, c.depth + 1    -- recursive member
    FROM dbo.employees AS e
    JOIN chain AS c ON e.manager_id = c.id
)
SELECT * FROM chain ORDER BY depth, first_name;
flowchart TD
  A["Anchor: CEO (depth 0)"] --> B["Round 1: direct reports (depth 1)"]
  B --> C["Round 2: their reports (depth 2)"]
  C --> D["Round 3: no more rows, stop"]

Each round joins the previous round's rows to the table; recursion ends when a round is empty.

SQL The organization chart ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

T-SQL does not use the RECURSIVE keyword; the CTE is recursive simply because it references itself. The editor above needs it, so that line is the labelled difference.

Limits and infinite loops

By default SQL Server stops a recursive CTE after 100 recursion levels and raises error 530. Raise or remove the limit with a query hint:

SQL
SELECT * FROM chain OPTION (MAXRECURSION 500);   -- 0 means no limit

If the data contains a cycle (A manages B and B manages A) the recursion never ends and hits the limit, so validate hierarchical data or track the visited path (as the path column above does) and add a condition such as WHERE c.path NOT LIKE '%' + e.first_name + '%'.

Generating series

Recursive CTEs can generate numbers or dates without a table: start at 1, add 1 until you reach a bound. It is handy for calendars or filling gaps:

SQL
WITH days AS (
    SELECT CAST('2025-03-01' AS DATE) AS d
    UNION ALL
    SELECT DATEADD(day, 1, d) FROM days WHERE d < '2025-03-07'
)
SELECT d FROM days;

SQL Server 2022 adds GENERATE_SERIES(1, 100) for numeric ranges, which is simpler and faster. A calendar generated this way is typically cross joined or left joined to your data to find days with no activity:

SQL Dates with no orders (calendar CTE) ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

CTE versus temp table versus view

CTE Temp table View
Lives for one statement the session or procedure permanently
Stores data no yes (can be indexed) no
Reusable across statements no yes yes
Can be recursive yes no via a CTE inside

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. How long does a CTE exist?

    A CTE is a statement-scoped name; to reuse a result across statements use a temp table, table variable or view.
  2. 2. What are the two parts of a recursive CTE joined by UNION ALL?

    The anchor produces starting rows; the recursive member references the CTE to produce each next level.
  3. 3. What is the default maximum recursion level in SQL Server?

    The default is 100. Use OPTION (MAXRECURSION n) to change it; 0 removes the limit.
  4. 4. Can you DELETE directly from a CTE in T-SQL?

    T-SQL allows UPDATE and DELETE against a CTE; the change applies to the base table, which is the idiom for removing duplicates with ROW_NUMBER.

Summary

  • WITH name AS (query) defines a statement-scoped result set; chain several with commas.
  • The statement before WITH must end with ;.
  • CTEs can be the target of UPDATE/DELETE, the idiom for deduplication.
  • Recursive CTEs have an anchor and a recursive member; mind MAXRECURSION and cycles.
  • Use a temp table instead when you must reuse an expensive result several times.

Next: Window Functions