CTE Recursion | Sequence, Dates, Factorial, Fibonacci series
CTE, Common Table Expressions
According to MS BOL CTE can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement.
A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.
More info on CTE can be found here: http://msdn.microsoft.com/en-us/library/ms190766.aspx
Some examples of CTE Recursion or Recursive CTE can be found here:
-- Number Sequence: 1 to 10 ;with num_seq as ( select 1 as num union all select num+1 from num_seq where num<100) select num from num_seq where num < 10 -- Date Sequence: May 2011 ;with dt_seq as ( select cast('5/1/2011' as datetime) as dt, 1 as num union all select dt+1, num+1 from dt_seq where num<31) select dt from dt_seq -- Factorial ;with fact as ( select 1 as fac, 1 as num union all select fac*(num+1), num+1 from fact where num<12) select fac from fact where num=5 -- Fibonacci Series ;with fibo as ( select 0 as fibA, 0 as fibB, 1 as seed, 1 as num union all select seed+fibA, fibA+fibB, fibA, num+1 from fibo where num<12) select fibA from fibo
More on Recursive CTE: http://msdn.microsoft.com/en-us/library/ms186243.aspx
Categories: SQL Tips
CTE, CTE recursion, Recursive CTE
— Number Sequence: 1 to 10
;with num_seq as (
select 1 as num
union all
select num+1
from num_seq
where num<100)
select num
from num_seq
where num < 10
Small correction.
Hi Arun,
Thanks for noticing this and posting the correction 🙂
To me it seems these examples are useful only when generating small lists of sequences otherwise you either receive an error like this:
Msg 530, Level 16, State 1, Line 2
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.
Or you change a parameter to permit thousands of recursive calls which to me seems like the wrong approach. Are these illustarative examples or techniques you would suggest using?
Thanks and great blog!
Hi Jeff,
Yes you are right. Recursive CTEs could be expensive when used with larger sets of data. And you also cannot predict their behavior and performance.
The examples are just to illustrate the features of CTEs.
More better approach is to use a separate pre-populated table for either of the mentioned examples.
Thanks for your comments.
~manoj
Out of curiosity, why is the column fibB necessary? This of course is in regard to the Fibonacci Series.
Thanks Kevin, you your comment.
Yes, fibB column will not be necessary if you write the logic in other or more optimized way.