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…