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:
MAXRECURSIONquery hint (a value between 1 and 32767) and an error occurs.
MAXRECURSIONquery hint (a value of 0) and crash your server due to an infinite loop.
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.