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.

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

About these ads
  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. Jeff Capeci
    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.

      Thanks for your comments.

      ~manoj

  3. kevin cain
    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

      Thanks Kevin, you your comment.
      Yes, fibB column will not be necessary if you write the logic in other or more optimized way.

  1. May 29, 2011 at 6:44 am
  2. July 29, 2011 at 9:52 am
  3. August 18, 2011 at 1:20 pm
  4. November 14, 2012 at 5:23 am
  5. November 19, 2012 at 12:37 am
  6. June 30, 2014 at 7:58 am

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 397 other followers

%d bloggers like this: