Tuesday, March 29, 2016

Database : MAXRECURSION

The value <MAXRECURSION Value> specified for the MAXRECURSION option exceeds the allowed maximum of 32767.
SQL Server Error Messages - Msg 310 - The value <MAXRECURSION Value> specified for the MAXRECURSION option exceeds the allowed maximum of 32767.
SQL Server Error Messages - Msg 310
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.
---------------------------------------------------------------------

Specify the maxrecursion option at the end of the query:
...
from EmployeeTree
option (maxrecursion 0)
That allows you to specify how often the CTE can recurse before generating an error. Maxrecursion 0 allows infinite recursion

------------------------------------------------------

You can limit the number of recursion levels using the MAXRECURSION option hint like this: OPTION (MAXRECURSION 0); where the value (between 0 and 32767) specifies the number of levels of recursion, 0 meaning infinite.
From the documentation for CTE:
An incorrectly composed recursive CTE may cause an infinite loop. For example, if the recursive member query definition returns the same values for both the parent and child columns, an infinite loop is created. To prevent an infinite loop, you can limit the number of recursion levels allowed for a particular statement by using the MAXRECURSION hint and a value between 0 and 32,767 in the OPTION clause of the INSERT, UPDATE, DELETE, or SELECT statement. This lets you control the execution of the statement until you resolve the code problem that is creating the loop. The server-wide default is 100. When 0 is specified, no limit is applied. Only one MAXRECURSION value can be specified per statement. For more information, see Query Hints (Transact-SQL).
MAXRECURSION number
Specifies the maximum number of recursions allowed for this query. Number is a nonnegative integer between 0 and 32767. When 0 is specified, no limit is applied. If this option is not specified, the default limit for the server 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.
Because of this error, all effects of the statement are rolled back. If the statement is 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.
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)