Advertisements
Home > SQL Tips > Recursive CTE error – The maximum recursion 100 has been exhausted before statement completion

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

Advertisements
  1. Jeff Moden
    January 30, 2017 at 5:40 pm

    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.

    • January 30, 2017 at 5:55 pm

      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

  2. shanmugam c
    January 30, 2017 at 1:11 pm

    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’

  3. January 23, 2017 at 7:47 am

    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

  4. February 8, 2015 at 10:36 am

    OPTION (MAXRECURSION 1000)
    doesnt work in a view. any alternatives ?

    • February 9, 2015 at 1:20 pm

      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);

  5. February 8, 2015 at 10:36 am

    hey, OPTION (MAXRECURSION 1000) doesnt work in a view.. any alternatives ?

  6. Dinesh
    October 29, 2014 at 11:18 am

    Thank you…

  7. Shatrughna Kumar
    December 23, 2011 at 1:06 pm

    Nice Information.

  1. January 17, 2013 at 10:31 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: