How many maximum recursion possible for CTE in SQL server?

Question

How many maximum recursion level possible for CTE in SQL server? If maximum recursion level is reached then what are the alternative way?

Answer 1

By default, it's 100.

You can change it on per-query basis by appending a hint:

OPTION (MAXRECURSION = N)

to the end of the query.

OPTION (MAXRECURSION = 0)

means no explicit limit.

When the recursion limit is reached, an error is raised and the query breaks.