|
|
Error Message
Server: Msg 310, Level 15, State 1, Line 1
The value <MAXRECURSION Value> specified for the
MAXRECURSION option exceeds the allowed maximum
of 32767.
Causes
The MAXRECURSION query hint specifies the maximum number of recursions allowed for a query. The number of recursions is a non-negative integer between 0 and 32,767. When 0 is specified, no limit is applied.
|
If the MAXRECURSION query hint is not specified, the default limit is 100. When the specified or default number for MAXRECURSION limit is reached during query execution, the query is ended and an error is returned.
If the MAXRECURSION query hint is specified in a SELECT statement, partial results or no results may be returned. Any partial results returned may not include all rows on recursion levels beyond the specified maximum recursion level.
As mentioned above, the maximum number of recursions allowed for a query is 32,767 with a default value of 100 if the MAXRECURSION query hint is not specified. To illustrate on how this error can easily be generated, the following common table expression (CTE) is used:
WITH [Numbers] AS (
SELECT 1 AS [Number]
UNION ALL
SELECT [Number] + 1
FROM [Numbers]
WHERE [Number] < 100
)
SELECT * FROM [Numbers]
GO
This common table expression (CTE) generates a table with a column containing a value from 1 to 100. This simple CTE is useful in different ways. One use for this numbers table is generating a sequence of dates:
WITH [Numbers] AS (
SELECT 1 AS [Number]
UNION ALL
SELECT [Number] + 1
FROM [Numbers]
WHERE [Number] < 100
)
SELECT DATEADD(DD, 0, DATEDIFF(DD, 0, GETDATE())) + [Number]
FROM [Numbers]
GO
Another use of the numbers CTE table is identifying missing identity values within a table:
DECLARE @Identifiers TABLE (
[ID] INT
)
INSERT INTO @Identifiers ( [ID] )
VALUES ( 1 ), ( 3 ), ( 4 ), ( 7 );
WITH [Numbers] AS (
SELECT 1 AS [Number]
UNION ALL
SELECT [Number] + 1
FROM [Numbers]
WHERE [Number] < 10
)
SELECT A.[Number]
FROM [Numbers] A LEFT OUTER JOIN @Identifiers B
ON A.[Number] = B.[ID]
WHERE B.[ID] IS NULL
GO
As mentioned earlier, if the MAXRECURSION query hint is not specified, the default maximum number of recursions is 100. Given this, if the number table CTE above is modified to return a table containing numbers 1 to 1000, as follows:
WITH [Numbers] AS (
SELECT 1 AS [Number]
UNION ALL
SELECT [Number] + 1
FROM [Numbers]
WHERE [Number] < 1000
)
SELECT * FROM [Numbers]
GO
The following error message is encountered:
Msg 530, Level 16, State 1, Line 1
The statement terminated.
The maximum recursion 100 has been exhausted before statement completion.
To overcome this error message, the MAXRECURSION query hint can be specified to increase the maximum number of recursion from the default value of 100 to a maximum of 1000.
WITH [Numbers] AS (
SELECT 1 AS [Number]
UNION ALL
SELECT [Number] + 1
FROM [Numbers]
WHERE [Number] < 1000
)
SELECT * FROM [Numbers]
OPTION (MAXRECURSION 1000)
GO
To return a table that contains values from 1 to 50,000, the common table expression above is now modified to increase the maximum number of recursions to 50,000, as follows:
WITH [Numbers] AS (
SELECT 1 AS [Number]
UNION ALL
SELECT [Number] + 1
FROM [Numbers]
WHERE [Number] < 50000
)
SELECT * FROM [Numbers]
OPTION (MAXRECURSION 50000)
GO
Unfortunately, the maximum number of recursions allowed for a recursive CTE is only 32,767. Given this, the previous common table expression will generate the following error message:
Msg 310, Level 15, State 1, Line 10
The value 50000 specified for the MAXRECURSION option exceeds the allowed maximum of 32767.
Solution / Work Around:
Since the maximum number of recursions allowed is only 32,767, the only way to overcome this limitation is to re-write the query and replace the common table expression (CTE) with an actual table, whether it be a temporary table or a table variable, that will hold the values, in this case the numbers in the numbers table.
Using a table variable, here’s one way of generating a table containing numbers from 1 to 50,000:
DECLARE @Numbers TABLE (
[Number] INT
)
DECLARE @Counter INT
SET @Counter = 1
WHILE @Counter <= 50000
BEGIN
INSERT INTO @Numbers ( [Number] )
VALUES ( @Counter )
SET @Counter = @Counter + 1
END
SELECT * FROM @Numbers
GO
Here’s another way of generating a table containing number from 1 to 50,000 using a common table expression:
WITH [Numbers] AS (
SELECT 0 AS [Number]
UNION ALL
SELECT [Number] + 1
FROM [Numbers]
WHERE [Number] < 999
)
SELECT [Thousands].[Number] * 1000 + [Ones].[Number]
FROM [Numbers] [Thousands] CROSS JOIN [Numbers] [Ones]
ORDER BY 1
OPTION (MAXRECURSION 1000)
GO
This is just a simple output from a common table expression. More complicated requirements that require a recursion of more than 32,767 will not be able to make use of a common table expression and may need to use a table variable to accomplish the requirement.
|