### Archive

Archive for May 23, 2011

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