Generating number sequences with Common Table Expressions

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Rafal S. » Generating number sequences with Common Table...

Generating number sequences with Common Table Expressions

Rate Topic

Display Mode

Topic Options

Author

Message

skra

Posted Saturday, February 09, 2008 7:11 AM

Say Hey Kid

Group: General Forum Members
Last Login: Yesterday @ 4:11 AM
Points: 711, Visits: 210

Comments posted to this topic are about the item Generating number sequences with Common Table Expressions

Post #453583

ChrisM@Work

Posted Tuesday, October 04, 2011 2:53 AM

SSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:27 AM
Points: 5,618, Visits: 10,990

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

nigel.

Posted Tuesday, October 04, 2011 3:03 AM

Ten Centuries

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 7:31 AM
Points: 1,164, Visits: 2,601

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

skra

Posted Tuesday, October 04, 2011 3:12 AM

Say Hey Kid

Group: General Forum Members
Last Login: Yesterday @ 4:11 AM
Points: 711, Visits: 210

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

nigel.

Posted Tuesday, October 04, 2011 3:20 AM

Ten Centuries

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 7:31 AM
Points: 1,164, Visits: 2,601

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

ChrisM@Work

Posted Tuesday, October 04, 2011 3:23 AM

SSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:27 AM
Points: 5,618, Visits: 10,990

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}

Post #1185106

« Prev Topic | Next Topic »

Permissions