Introduction
The very basics
Infinite looping
13. MAXRECURSION n
Processing trees with recursive CTEs
Processing graphs with recursive ctes
Summary

Instruction

As you could see, our query terminated with an error. SQL Server has a built-in termination check for recursive CTEs. In the default setting, it only allows for a maximum recursion depth of 100. If your recursion exceeds a depth of 100, the query will terminate.

However, sometimes you may want to change that. A 100 depth might not be enough, or you may know that some complicated computations should end sooner. That's why there is the MAXRECURSION n option. The usage looks like this:

WITH Counter(PreviousValue) AS (
  SELECT
    1  -- anchor member
  UNION ALL
  SELECT
    PreviousValue + 1  -- recursive member
  FROM Counter
)

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

The OPTION (MAXRECURSION 150) tells SQL Server that you want to override the default max recursion depth and set it to 150.

The valid values for this option are all integers between 0 and 32,767. However, 0 is a special value and we will discuss it later.

Exercise

Do you remember the exercise with appending numbers? We have added the OPTION (MAXRECURSION 2) to the query. Try running the template and see what happens.

You can play around with MAXRECURSION and the termination check and see what happens. Click I'm done. Next exercisewhen you're ready to move on.