Recursive CTEs

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.

Leave a Reply

*

Panorama Theme by Themocracy