Archive
Archive for May 23, 2011
CTE Recursion | Sequence, Dates, Factorial, Fibonacci series
May 23, 2011
14 comments
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