Alex Meyer-Gleaves

on sql server

Limiting CTE recursion depth in SQL Server

I can think of a few different cases that will result in the recursion of a CTE (Common Table Expression) in SQL Server coming to an end:

Obviously, some of these cases have a more desirable outcome than others. While the MAXRECURSION query hint does provide a mechanism to ensure you do not end up in an infinite loop, reaching the limit it imposes causes an error to occur, and that is something to be avoided.

It is also possible to set the MAXRECURSION query hint to 0 which will result in no limit being applied, so without a mechanism of your own in place to limit the recursion depth, you have definitely made it easier to get yourself into trouble.

I prefer to manually restrict the recursion level and set the MAXRECURSION query hint to be the same if my restriction is greater than 100. Remember, 100 is the default limit when the MAXRECURSION query hint is not present, and you must exceed and not just reach the limit for the error to occur.

The query below is manually limited to 50 levels of recursion. There is no need to specify the MAXRECURSION query hint because the default of 100 will never be reached.

WITH LimitedLoop AS
(
    SELECT 0 AS RecursionLevel

    UNION ALL

    SELECT (LimitedLoop.RecursionLevel + 1) AS RecursionLevel
    FROM LimitedLoop
    WHERE (LimitedLoop.RecursionLevel + 1) <= 50
)
SELECT * FROM LimitedLoop

This query is manually limited to 200 levels of recursion. The MAXRECURSION query hint is specified because its default value of 100 is less than the manually imposed limit.

WITH LimitedLoop AS
(
    SELECT 0 AS RecursionLevel

    UNION ALL

    SELECT (LimitedLoop.RecursionLevel + 1) AS RecursionLevel
    FROM LimitedLoop
    WHERE (LimitedLoop.RecursionLevel + 1) <= 200
)
SELECT * FROM LimitedLoop
OPTION (MAXRECURSION 200)

It is also worth noting that the first query will return 51 rows and the second 201 rows. This is because the first record returned containing the 0 in the RecursionLevel column is part of the base result set and not the recursive invocation.

profile
AUTHOR

Alex Meyer-Gleaves

I'm a Technical Architect living in Australia (that island like continent in the southern hemisphere). I love Microsoft .NET and C#. I hate early mornings, slow drivers and Lotus Notes.