Recursive CTE
A recursive CTE is nothing but it is a common table expression
that references itself.
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.
