Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Generating number sequences with Common Table Expressions Expand / Collapse
Author
Message
Posted Saturday, February 9, 2008 7:11 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Tuesday, September 30, 2014 2:52 AM
Points: 711, Visits: 222
Comments posted to this topic are about the item Generating number sequences with Common Table Expressions
Post #453583
Posted Tuesday, October 4, 2011 2:53 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, December 18, 2014 1:43 AM
Points: 6,890, Visits: 14,254
Try option (MAXRECURSION 0).

This is by far the slowest of the CTE-based row (number) generation methods but as your code shows it's very easy to manipulate rCTE's to generate quite complex output.


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1185092
Posted Tuesday, October 4, 2011 3:03 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, December 18, 2014 4:43 AM
Points: 1,184, Visits: 2,680
Have a look at this too, by Itzik Ben-Gan:
http://www.sqlmag.com/article/sql-server/virtual-auxiliary-table-of-numbers

Should perform better than a recursive CTE.


--
Nigel
Useful Links:
How to post data/code on a forum to get the best help
The "Numbers" or "Tally" Table - Jeff Moden

Post #1185095
Posted Tuesday, October 4, 2011 3:12 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Tuesday, September 30, 2014 2:52 AM
Points: 711, Visits: 222
I am surprised that anyone is still reading it since it was published in february 2008 :)

And about the Itzik Ben-Gan article (http://www.sqlmag.com/article/sql-server/virtual-auxiliary-table-of-numbers)

I also published script which uses similar method in january 2008:

http://www.sqlservercentral.com/scripts/61950/
Post #1185099
Posted Tuesday, October 4, 2011 3:20 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, December 18, 2014 4:43 AM
Points: 1,184, Visits: 2,680
skra (10/4/2011)
I am surprised that anyone is still reading it since it was published in february 2008 :)

It is the "Featured Script" in todays news letter.


--
Nigel
Useful Links:
How to post data/code on a forum to get the best help
The "Numbers" or "Tally" Table - Jeff Moden

Post #1185104
Posted Tuesday, October 4, 2011 3:23 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, December 18, 2014 1:43 AM
Points: 6,890, Visits: 14,254
nigel. (10/4/2011)
skra (10/4/2011)
I am surprised that anyone is still reading it since it was published in february 2008 :)

It is the "Featured Script" in todays news letter.


What's "today"?

By Rafal Skotak, 0001/01/01
Total article views: 1163 | Views in the last 30 days: 132


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1185106
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse