How many maximum recursion possible for CTE in SQL server?

How many maximum recursion level possible for CTE in SQL server? If maximum recursion level is reached then what are th开发者_开发问答e alternative way?

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.