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 (
1 -- anchor member
PreviousValue + 1 -- recursive member
FROM Counter -- invocation
OPTION (MAXRECURSION 150);
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.