Recursive CTE – maximum recursion 100 has been exhausted
One of the most benefit of CTE (Common Table Expressions) is that we can create recursive queries with them. In my previous posts I’ve discussed this topic with some good examples, link.
An incorrectly composed recursive CTE may cause an infinite loop. So recursive CTEs should be designed very carefully and the recursion level should be checked. To prevent it to run infinitely SQL Server’s default recursion level is set to 100. But you can change the level by using the MAXRECURSION option/hint. The recursion level ranges from 0 and 32,767.
If your CTEs recursion level crosses the limit then following error is thrown by SQL Server engine:
Msg 530, Level 16, State 1, Line 11
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.
Let’s check this with an example discussed in MSDN TSQL forum, link:
DECLARE @startDate DATETIME, @endDate DATETIME SET @startDate = '11/10/2011' SET @endDate = '03/25/2012' ; WITH CTE AS ( SELECT YEAR(@startDate) AS 'yr', MONTH(@startDate) AS 'mm', DATENAME(mm, @startDate) AS 'mon', DATEPART(d,@startDate) AS 'dd', @startDate 'new_date' UNION ALL SELECT YEAR(new_date) AS 'yr', MONTH(new_date) AS 'mm', DATENAME(mm, new_date) AS 'mon', DATEPART(d,@startDate) AS 'dd', DATEADD(d,1,new_date) 'new_date' FROM CTE WHERE new_date < @endDate ) SELECT yr AS 'Year', mon AS 'Month', count(dd) AS 'Days' FROM CTE GROUP BY mon, yr, mm ORDER BY yr, mm OPTION (MAXRECURSION 1000)
Output:- Year Month Days 2011 November 22 2011 December 31 2012 January 31 2012 February 29 2012 March 24
Here, by applying “OPTION (MAXRECURSION 1000)”, we can set the recursion level, so that it does not go infinite.
Note: Restriction of recursive CTE is – “A view that contains a recursive CTE cannot be used to update data”.
More info on: http://msdn.microsoft.com/en-us/library/ms175972.aspx