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.
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 when you're ready to move on.
