Home > SQL Tips > CTE Recursion | Sequence, Dates, Factorial, Fibonacci series

## 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.

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
1. September 19, 2011 at 4:02 am

— 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.

• September 19, 2011 at 5:14 am

Hi Arun,
Thanks for noticing this and posting the correction 🙂

2. May 4, 2012 at 9:28 pm

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!

• May 4, 2012 at 9:33 pm

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.

~manoj

3. December 17, 2012 at 11:55 pm

Out of curiosity, why is the column fibB necessary? This of course is in regard to the Fibonacci Series.

• December 18, 2012 at 10:06 am