Generating number sequences with Common Table Expressions
-
Comments posted to this topic are about the item Generating number sequences with Common Table Expressions
-
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 ShawFor 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 -
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.
-
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:
-
skra (10/4/2011)
I am surprised that anyone is still reading it since it was published in february 2008It is the "Featured Script" in todays news letter.
-
nigel. (10/4/2011)
skra (10/4/2011)
I am surprised that anyone is still reading it since it was published in february 2008It 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 ShawFor 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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply