Recursive CTE error – The maximum recursion 100 has been exhausted before statement completion
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
Leave a comment
Current Visitors
StatCounter …since April 2012
Leisure blog: Creek & Trails
- An error has occurred; the feed is probably down. Try again later.
SQL with Manoj 
Nice Information.
Thank you…
hey, OPTION (MAXRECURSION 1000) doesnt work in a view.. any alternatives ?
OPTION (MAXRECURSION 1000)
doesnt work in a view. any alternatives ?
While creating VIEW do not use this clause. When you are executing the VIEW use it there instead, like:
SELECT col1, col2
FROM vwCTE
OPTION (MAXRECURSION 1000);
I personally would do something like this instead:
; WITH CTE AS (
SELECT
0 AS ‘Visible’,
1 AS ‘Days’,
YEAR(@startDate) AS ‘Year’,
MONTH(@startDate) AS ‘Month’,
DATENAME(mm, @startDate) AS ‘MonthName’,
@startDate ‘new_date’
UNION ALL
SELECT
1,
DATEDIFF(Day,
(CASE
WHEN (DATEPART(Year, new_date) = YEAR(@startDate)) AND (DATEPART(Month, new_date) = MONTH(@startDate)) THEN @startDate-1
ELSE CONVERT(DATETIME, CAST(MONTH(new_date) AS nvarchar(2)) + ‘/1/’ + CAST(YEAR(new_date) AS nvarchar(4)))
END),
(CASE
WHEN (DATEPART(Year, new_date) = YEAR(@endDate)) AND (DATEPART(Month, new_date) = MONTH(@endDate)) THEN @endDate
ELSE DATEADD(MONTH, 1, CONVERT(DATETIME, CAST(MONTH(new_date) AS nvarchar(2)) + ‘/1/’ + CAST(YEAR(new_date) AS nvarchar(4))))
END)),
YEAR(new_date),
MONTH(new_date),
DATENAME(mm, new_date),
DATEADD(m,1,new_date)
FROM CTE
WHERE new_date < @endDate
)
SELECT Year,Month,MonthName,Days FROM CTE WHERE Visible<>0
OPTION (MAXRECURSION 32767)
… This supports ALOT less recursion as it only loops through each month rather than every day … meaning that Recursions are less … with the max recursions yours supports 32767 days (about 90 years) where as mine supports 32767 months (2730 years)!…
Also the time difference is (with 90 years set):
Yours: 374ms
Mine: 31ms
does not seem to be correct.
change it as
UNION ALL
SELECT
YEAR(new_date+1) AS ‘yr’,
MONTH(new_date+1) AS ‘mm’,
DATENAME(mm, new_date+1) AS ‘mon’,
DATEPART(d,new_date+1) AS ‘dd’,
DATEADD(d,1,new_date) ‘new_date’
A better thing would be to avoid the rCTE altogether because it’s as slow as a While Loop and uses a whole lot more resources. I tried to post a solution yesterday but it hasn’t made it through whatever review process there is. It contained a high speed integer function (fnTally) and the code to solve this problem with zero reads.
You are correct @Jeff, Recursive CTEs are slow, and a tally table/function would be faster. I main motive of this post was to address the max-recursion error. Could you please repost your solution?
Thanks, Manoj
Apologies for the delay. I lost track of this thread. Here’s a solution that uses an inline Table Valued Function instead of a recursive CTE It also calculates the number of days in the first month and last month ranges correctly.
DECLARE @StartDate DATETIME = ’11/10/2011′
,@EndDate DATETIME = ’03/25/2012′
;
WITH cteMonthStartDates AS
(
SELECT StartDate = IIF(lo.MonthStartDate < @StartDate, @StartDate, lo.MonthStartDate)
,EndDate = IIF(hi.MonthEndDate > @EndDate, @EndDate, hi.MonthEndDate)
FROM dbo.fntally(0,DATEDIFF(mm,@StartDate,@EndDate)) t
CROSS APPLY (VALUES (DATEADD(mm,DATEDIFF(mm,0,@StartDate)+t.N,0)))lo(MonthStartDate)
CROSS APPLY (VALUES (EOMONTH(lo.MonthStartDate)))hi(MonthEndDate)
)
SELECT [Year] = DATEPART(yy,StartDate)
,[Month] = DATENAME(mm,StartDate)
,[Days] = DATEDIFF(dd,StartDate,EndDate)+1
,StartDate –Just for reference
,EndDate –Just for reference
FROM cteMonthStartDates
;
You can get a copy of the fnTally function at the following link.
https://www.sqlservercentral.com/scripts/create-a-tally-function-fntally
Here’s a link that explains why you shouldn’t use a recursive CTE to create incremental sequences, no matter how small they may be.
https://www.sqlservercentral.com/articles/hidden-rbar-counting-with-recursive-ctes
Thank you so much. Have been giving maxrecursion as 0 which never ended.
Your post really helped me.
But when data is increased it again throwing below error:
” The statement terminated. The maximum recursion 1000 has been exhausted before statement completion. ” Have increased up to 30,000 but still same error.
For now I have used a loop passing just one value at a time but loop is taking more time. Still better than direct CTE.