T-SQL: simple recursion exceeding max recursion depth

Question

I have a table my_table of the form

rowNumber    number   ...
1               23
2               14
3               15
4               25
5               19
6               21
7               19
8               37
9               31
        ...
1000            28

and I want to find the maximum length of an increasing consecutive sequence of the column number. For this example, it will be 3:

14, 15, 25

My idea is to calculate such length for each number:

rowNumber    number   ...   length
1               23            1
2               14            1
3               15            2
4               25            3
5               19            1
6               21            2
7               19            1
8               37            2
9               31            1
        ...

and then take the maximum. To calculate length, I wrote the following query that is using recursion:

with enhanced_table as (select *
                               ,1 length
                       from    my_table 
                       where   rowNumber = 1
                       union all
                       (select b.*
                               ,case when b.number > a.number 
                                     then a.length + 1 
                                     end new_column
                       from    enhanced_table a, my_table b 
                       where   b.rowNumber = a.rowNumber + 1
                       )
select  max(length)
from    enhanced_table

So, I'm trying to start from rowNumber = 1 and add all other rows consecutively by recursion. I'm getting the maximum recursion 100 has been exhausted before statement completion error.

My question is: should I find a way to increase maximum iterations allowed on the server (given that the query is simple, I think there won't be a problem to run 1000 iterations), or find another approach?

Also, isn't 100 iterations too low of a threshold?

Thank you!

Answer 1

There has to be some default threshold, and that is what Microsoft chose. It's to prevent infinite loops. Besides, looping doesn't perform well in SQL Server and goes against its set-based structure.

You can specify the max recursion you want to set for the individual query. This overrides the default.

select  max(length)
from    enhanced_table
option (maxrecursion 1000)

Note, option (maxrecursion 0) is the same as unlimited... and can cause an infinte loop

REFERENCE

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

Answer 2

If you wish to declare the maxrecursion parameter in the beginning of the query. You could try building query something like:

    DECLARE @Query NVARCHAR(MAX)
    SET @Query = N'
    ;WITH foo AS (
        ...
     )

    SELECT * FROM foo
    OPTION (MAXRECURSION ' + CAST(@maxrec AS NVARCHAR) + ');'

and the Execute it using Exec

You could go refer to this answer here:Maxrecursion parameter