## Recursive CTEs Comments (0)
By , April 18, 2015 09:39

For my own reference, here are two CTEs which use recursion and that is their only value. These examples would certainly not find usage in a “normal” environment.

The first is a result set where the returned value is double the previous value:

 ```use Work; with doubles_CTE (rowNumber, dVal) as ( select 1 , cast(1 as bigint) union all select rowNumber + 1 ,cast(dVal as bigint) * 2 from doubles_CTE where rowNumber < 62 --to aviod recursion issues and prevent arithmetic overflow BIGINT has an upper range of 2^63-1, numeric 10^38-1 ) select dVal from doubles_CTE option (maxrecursion 62) go ```

The second is the Fibonacci sequence:

 ```use Work; with Fibonacci_CTE (RowNum, Increment, Fibonacci) as ( select 1 , cast(0 as numeric(38,0)) , cast(1 as numeric(38,0)) union all select RowNum + 1 ,Fibonacci , Increment + Fibonacci from Fibonacci_CTE where RowNum < 183 --prevent aritmetic overflow numeric has an upper range of 10^38-1 ) select Fibonacci from Fibonacci_CTE option (maxrecursion 183)--to aviod recursion issues ```

If the sequence has to begin with 0 then use the following select:

 ```select 0 union all select Fibonacci from Fibonacci_CTE ```

Conclusion

It is important to note, that recursion can lead to performance issues an in these cases arithmetic overflow. CTEs have a built-in safety net and will only perform the recursion up to 100 times, which is the server default. If you want to override this, do so with caution.

Like I said, just for reference. I would like to revisit these to see if they can be simplified. Next time perhaps.