Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

Recursive Queries in MS SQL Server

Introduction
Infinite looping
14. MAXRECURSION 0
Processing trees with recursive CTEs
Processing graphs with recursive ctes
Summary

Instruction

Great! Now, you know how to change the recursion depth. However, we have mentioned that 0 is a special value. Why? It means that you don't set any limits. With this option, the query may run infinitely! This is very risky, but sometimes you may want to write a very deep recursion. In such queries, it is vital to include a termination check or you may have to restart the whole database! Take a look at a query without any recursion depth limit and with a proper termination check:

WITH Counter(PreviousValue) AS (
  SELECT
    1  -- anchor member
  UNION ALL
  SELECT
    PreviousValue + 1  -- recursive member
  FROM Counter
  WHERE PreviousValue < 100000  -- termination check
)

SELECT
  *
FROM Counter -- invocation
OPTION (MAXRECURSION 0);

The above query will stop thanks to the termination check, but it won't be stopped by max recursion depth.

Exercise

Run the template query. As you can see, we have shown values that wouldn't have been within reach without using MAXRECURSION.

On our page, all the queries that does't finish before reaching the recursion limit are killed by the site and does't return any rows.