Recursive CTE error: The maximum recursion 100 has been exhausted before statement completion (MAXRECURSION Option in CTE)

Recursive CTE

A recursive CTE is nothing but it is a common table expression that references itself.

Learn more about CTE:

https://bageshkumarbagi-msbi.blogspot.com/2016/05/cte-common-table-expressions-in-sql.html

Let’s see the example of recursive CTE

Here I am creating CTE to display the number from 1 to 20.

WITH CET_Number(number)  AS ( select 1 as number union all select number +1 as number from CET_Number where number<20 ) select * from CET_Number order by 1 desc

See the result

  

This is one simple example. Let’s do the recursion 150 times of this CTE.

 OMG!!!!     

   

Throwing error saying that “The statement terminated. The maximum recursion 100 has been exhausted before statement completion.”

It means it will not do the recursion more than 100 level.

   

But sometimes we need to perform more than 100 times.

With the help of MAXRECURSION option in CTE, we can overcome with this issue.

Note:

“By default maximum recursion level supported by CTE is 100. But CTE provides an option to change it by means of the MAXRECURSION hint. MAXRECURSION hint value can be between 0 to 32,767. Specifying its value as 0 means no limit.”

       

If we use 0, in this case, it means there is no limit of recursion. But be care full when we are using MAXRECURSION option with 0. If we will not write the proper exit condition then it will go to an infinite loop. See below

WITH CET_Number(number)  AS ( select 1 as number union all select number +1 as number from CET_Number ) select * from CET_Number order by 1 desc OPTION (MAXRECURSION 0)

In this query, I am using MAXRECURSION option with 0 and in this query I am not having any exit condition. It went to an infinite loop.

      

So be careful when we are using recursive CTE.