Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Maximum recursion possible with CTE in SQL Server 2005/2008

In my last blog about Tally Tables, we talked about the use of recursive CTEs to generate Tally Tables. Following that, someone quickly asked me to generate a Tally Table for integers starting from 1 to 150 as when he tried the way explained in my last post, the following exception was generated –

Msg 530, Level 16, State 1, Line 3

The statement terminated. The maximum recursion 100 has been exhausted before statement completion.


Now what to do?

Actually, to prevent infinite recursion, a default value for MAXRECURSION = 100 has been already set. Hence, any recursion will stop on reaching the threshold limit. If we want to loop/iterate more than the default value, we need to set the MAXRECURSION value as explained in my another post - Prevent recursive CTE from entering an infinite loop

So, the following statement will work to generate a Tally Table from 1 to 150–

DECLARE @Max AS INT = 150

 

;WITH CTE AS (

  SELECT 1 Num

  UNION ALL

  SELECT Num + 1 FROM CTE WHERE Num < @Max

)

 

SELECT * FROM CTE OPTION (MAXRECURSION 150);

 

After looking at this option, most of us will ask – what is the max value that could be used with the MAXRECURSION option? And the answer is – 32767. If we try to set a value greater than this,

SELECT * FROM CTE OPTION (MAXRECURSION 32768);

sql fires the following exception –


Msg 310, Level 15, State 1, Line 10

The value 32768 specified for the MAXRECURSION option exceeds the allowed maximum of 32767.

 

Hope, this post will make you understand few more facts related to the use of CTE with recursion.

Happy iterating…

Comments

Posted by jbreffni on 10 June 2011

Typo, line should say:

SELECT * FROM CTE OPTION (MAXRECURSION 32768);

Posted by alexms_2001 on 10 June 2011

On max depth: if MAXRECURSION is set to 0, there is no limit at all, so that the following works:

DECLARE @Max AS INT = 200000;

WITH tally AS (SELECT 1 Num UNION ALL SELECT Num + 1 FROM tally WHERE Num < @Max)

SELECT * FROM tally OPTION (MAXRECURSION 0);

Posted by Jeff Moden on 24 September 2011

Please see the article at the following link for why you should NEVER generate such values with a recursive CTE.

www.sqlservercentral.com/.../74118

Leave a Comment

Please register or log in to leave a comment.