In my previous blog post, we discussed the use of recursive CTEs to generate Tally Tables. However, one of the readers encountered an issue when trying to generate a Tally Table for integers starting from 1 to 150. They received the following exception: “Msg 530, Level 16, State 1, Line 3 The statement terminated. The maximum recursion 100 has been exhausted before statement completion.”

This exception occurs because SQL Server has a default value for MAXRECURSION, which is set to 100. This default value is in place to prevent infinite recursion. When the recursion reaches the threshold limit, it automatically stops. In order to generate a Tally Table with more than 100 iterations, we need to manually set the MAXRECURSION value.

To address this issue, we can modify the code as follows:

DECLARE @Max AS INT = 150;

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

SELECT * FROM CTE OPTION (MAXRECURSION 150);

By adding the “OPTION (MAXRECURSION 150)” clause at the end of the query, we can set the MAXRECURSION value to 150, allowing us to generate the desired Tally Table.

Now, you might be wondering what the maximum value for MAXRECURSION is. The answer is 32767. If we try to set a value greater than this, such as “SELECT * FROM CTE OPTION (MAXRECURSION 32768);”, SQL Server will throw an exception: “Msg 310, Level 15, State 1, Line 10 The value 32768 specified for the MAXRECURSION option exceeds the allowed maximum of 32767.”

Understanding the concept of MAXRECURSION is important when working with recursive CTEs. It allows us to control the number of iterations and prevent infinite loops. By setting the MAXRECURSION value appropriately, we can generate Tally Tables or perform other recursive operations efficiently.

Thank you for reading this blog post. I hope it has provided you with a better understanding of the MAXRECURSION option in SQL Server. Happy iterating!