Generating number sequences with Common Table Expressions

Question

Generating number sequences with Common Table Expressions

skra

SSCommitted

Points: 1614
More actions
February 9, 2008 at 7:11 am

#181801

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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

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

nigel. SSChampion Points: 11636 More actions · Answer 2

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
How to post questions

skra SSCommitted Points: 1614 More actions · Answer 3

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/

nigel. SSChampion Points: 11636 More actions · Answer 4

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
How to post questions

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

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