What does a non-recursive CTE actually buy you over a subquery?

Readability and reuse within one statement — author_revenue reads top to bottom as a named, self-contained step, and the same CTE name can be referenced more than once in the outer query without repeating the subquery text. It doesn't change the query plan by itself; SQL Server can still choose to inline it exactly as if it were a subquery.

Why did the date-series CTE fail with 'maximum recursion 100 has been exhausted'?

SQL Server caps a recursive CTE at 100 recursive invocations by default specifically to catch runaway recursion (a missing or wrong termination condition) before it consumes unbounded server resources. Generating dates from 2024-01-01 to 2026-09-13 needs 987 recursive steps, comfortably past that default, so it hits the cap and stops rather than silently running forever.

Is OPTION (MAXRECURSION 0) always the right fix?

0 means unlimited, which works but removes the safety net entirely — a genuinely broken termination condition (one that never becomes false) will now spin until it exhausts memory or the query is cancelled manually, instead of failing fast at 100. Setting a specific higher number (as this lesson does with 1100) keeps a safety ceiling while accommodating the real recursion depth needed.

Does the 100 recursion limit count the anchor row?

No — a span needing exactly 100 recursive steps (101 total rows: 1 anchor + 100 recursive) succeeds under the default limit, confirmed directly in this lesson's third practice task. The cap is on recursive invocations of the query, not on the total row count the CTE produces.

Can a CTE reference itself more than once, or reference another CTE defined earlier in the same WITH clause?

A later CTE in the same WITH clause can reference an earlier one (as top_books references book_totals in this lesson) — that's normal chaining. A recursive CTE, though, can only reference itself exactly once in its recursive member; SQL Server doesn't support a recursive CTE unioning multiple recursive self-references together.

Recursive CTEs and the Default MAXRECURSION 100 Limit

A recursive CTE has a built-in safety limit most non-recursive queries never bump into — until one generates more than 100 rows recursively.

A non-recursive CTE: naming a step

WITH author_revenue AS (
    SELECT a.name AS author_name, SUM(b.price * oi.quantity) AS revenue
    FROM authors a
    JOIN books b ON b.author_id = a.author_id
    JOIN order_items oi ON oi.book_id = b.book_id
    GROUP BY a.name
)
SELECT TOP 5 author_name, revenue
FROM author_revenue
ORDER BY revenue DESC;

author_name    revenue
-------------- ---------
Author 9       22453.59
Author 49      22443.59
Author 19      22403.60
Author 39      22353.61
Author 29      22323.61

(5 rows affected)

author_revenue names the join-and-aggregate step so the outer SELECT reads cleanly — no different from wrapping the same logic in a subquery, just more readable once a query has several steps chained together.

The bug: a recursive CTE past the default recursion limit

WITH date_series AS (
    SELECT CAST('2024-01-01' AS DATE) AS d
    UNION ALL
    SELECT DATEADD(DAY, 1, d) FROM date_series WHERE d < '2026-09-13'
)
SELECT COUNT(*) AS total_days FROM date_series;

Msg 530, Level 16, State 1, Server ..., Line 1
The statement terminated. The maximum recursion 100 has been exhausted before statement
completion.

The anchor (CAST('2024-01-01' AS DATE)) and the recursive member (DATEADD(DAY, 1, d) ...) are both correct — the termination condition (d < '2026-09-13') does eventually become false. The query simply needs 987 recursive steps to get there, and SQL Server’s default cap is 100, specifically to catch runaway recursion before it happens. This isn’t runaway; it’s just a real recursion depth past the conservative default.

The fix: OPTION (MAXRECURSION n)

WITH date_series AS (
    SELECT CAST('2024-01-01' AS DATE) AS d
    UNION ALL
    SELECT DATEADD(DAY, 1, d) FROM date_series WHERE d < '2026-09-13'
)
SELECT COUNT(*) AS total_days FROM date_series
OPTION (MAXRECURSION 1100);

total_days
-----------
       987

(1 rows affected)

OPTION (MAXRECURSION 1100) raises the cap enough to cover the real 987 steps needed, while still keeping a ceiling — a genuinely broken recursive CTE (a termination condition that never triggers) would still fail cleanly at 1100 instead of spinning forever. MAXRECURSION 0 removes the cap entirely; prefer a specific number when the real depth is knowable.

Practice

1. Use MAXRECURSION 0 instead of a specific number, and confirm it also works.

WITH date_series AS (
    SELECT CAST('2024-01-01' AS DATE) AS d
    UNION ALL
    SELECT DATEADD(DAY, 1, d) FROM date_series WHERE d < '2026-09-13'
)
SELECT COUNT(*) AS total_days FROM date_series
OPTION (MAXRECURSION 0);

total_days
-----------
       987

(1 rows affected)

Same result, no cap at all this time — works here, but offers no protection if the termination condition were ever wrong.

2. Chain two CTEs together — one referencing the other in the same WITH clause.

WITH book_totals AS (
    SELECT book_id, SUM(quantity) AS total_qty
    FROM order_items
    GROUP BY book_id
),
top_books AS (
    SELECT TOP 3 book_id, total_qty FROM book_totals ORDER BY total_qty DESC
)
SELECT b.title, t.total_qty
FROM top_books t JOIN books b ON b.book_id = t.book_id
ORDER BY t.total_qty DESC;

title             total_qty
----------------- -----------
Book Title 687    9
Book Title 1420   9
Book Title 1443   9

(3 rows affected)

top_books references book_totals directly — a comma separates multiple CTE definitions in one WITH clause, and later ones can build on earlier ones without repeating their logic.

3. Find the exact boundary: does a 100-step recursion succeed under the default limit?

WITH date_series AS (
    SELECT CAST('2024-01-01' AS DATE) AS d
    UNION ALL
    SELECT DATEADD(DAY, 1, d) FROM date_series WHERE d < '2024-04-10'
)
SELECT COUNT(*) AS total_days FROM date_series;

total_days
-----------
       101

(1 rows affected)

Succeeds with no OPTION needed — 100 days from 2024-01-01 to 2024-04-09 plus the anchor row is 101 total rows from exactly 100 recursive invocations, right at the default limit without exceeding it.

Next: transactions and isolation levels — a real dirty read, reproduced.

A non-recursive CTE: naming a step

The bug: a recursive CTE past the default recursion limit

The fix: OPTION (MAXRECURSION n)

Practice

Frequently Asked Questions