Post reply

Generating number sequences with Common Table Expressions

  • February 9, 2008 at 7:11 am

    #181801

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

  • October 4, 2011 at 2:53 am

    #1390002

    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

  • October 4, 2011 at 3:03 am

    #1390005

    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

  • October 4, 2011 at 3:12 am

    #1390007

    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/

  • October 4, 2011 at 3:20 am

    #1390011

    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

  • October 4, 2011 at 3:23 am

    #1390013

    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