Best April deals - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
The very basics
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.