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.

    [font="Arial"]β€œWrite the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]


    For fast, accurate and documented assistance in answering your questions, please read this article[/url].
    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]
    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
    [url

  • 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:

    http://www.sqlservercentral.com/scripts/61950/

  • 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. (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

    [font="Arial"]β€œWrite the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]


    For fast, accurate and documented assistance in answering your questions, please read this article[/url].
    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]
    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
    [url

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

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