How many maximum recursions are possible for CTEs on an SQL server?

Question

How much maximum recursion level is possible for CTE on SQL server? If the maximum level of recursion is reached, what is the alternative way?

Answer 1

By default it is 100.

You can change it for each request by adding a hint:

OPTION (MAXRECURSION N)

until the end of the request.

OPTION (MAXRECURSION 0)

means no explicit restriction.

When the recursion limit is reached, an error occurs and the request aborts.