Tally generator

  • Sergiy

    SSC Guru

    Points: 109840

    Comments posted to this topic are about the item Tally generator

  • Jeff Moden

    SSC Guru

    Points: 997119

    Can't believe whomever the editor was didn't catch the missing "y" in the title.  They can fix it for you, Sergiy.

    Good to see you've finally picked up the pen on SSC.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Sergiy

    SSC Guru

    Points: 109840

    I had status "Commited" for 2 years in a busy Production system.

    Not a soul complained.

    Till I spotted and fixed it myself.

    πŸ™‚

  • Jeff Moden

    SSC Guru

    Points: 997119

    Sergiy - Thursday, February 16, 2017 10:36 PM

    I had status "Commited" for 2 years in a busy Production system.Not a soul complained.Till I spotted and fixed it myself.:-)

    I'm more concerned that people won't find your script if they do a site search for the word "Tally".

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Sergiy

    SSC Guru

    Points: 109840

    I posted it only because it's used in the article scripts, so if it gets published I can refer to this post.

  • Jeff Moden

    SSC Guru

    Points: 997119

    Oh yeah... I got that from the beginning.  Still, it's useful by itself and it would be nice if people could find it.  But, it's your script.  I'm just making a suggestion.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • ChrisM@Work

    SSC Guru

    Points: 186120

    Nice, Sergiy, and very useful.
    I'm guessing that you've checked out all of the alternatives to get rid of the row count spool. I've only had a little time to play today and came up with this:

    DECLARE

            @StartValue bigint = null, -- -32768,

         @EndValue bigint = null, -- 32767,

            @Rows INT = 980, -- number of rows to bereturned. Used only when either @StartValue or @EndValue is not supplied

         @Increment smallint=3;

     

    WITH BaseNum (N) as (

           select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all

           select 1 union all select 1 union all select 1 union all select 1 union all select 1

           ),

           L1 (N) as (SELECT 1 FROM BaseNum bn1 CROSS JOIN BaseNum bn2),

           L2 (N) as (SELECT 1 FROM L1 a1 CROSS JOIN L1 a2),

           _Tally (N) as (

                  SELECT TOP(ISNULL(@EndValue - @StartValue + @Increment,@Rows)/@Increment)

                         rn= ISNULL(@StartValue,0) + ((ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1)*@Increment)

                  FROM L2 a1 CROSS JOIN L2 a2 CROSS JOIN L2 a3

                  )

    SELECT * FROM _Tally

    [/code]

    [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

  • George W (no relation)

    Ten Centuries

    Points: 1279

    I actually came to the article because I wanted to find out what a "Tall Generator" was :).

  • Sergiy

    SSC Guru

    Points: 109840

    ChrisM@Work - Friday, February 17, 2017 9:38 AM

    Nice, Sergiy, and very useful.
    I'm guessing that you've checked out all of the alternatives to get rid of the row count spool. I've only had a little time to play today and came up with this:

    DECLARE

            @StartValue bigint = null, -- -32768,

         @EndValue bigint = null, -- 32767,

            @Rows INT = 980, -- number of rows to bereturned. Used only when either @StartValue or @EndValue is not supplied

         @Increment smallint=3;

     

    WITH BaseNum (N) as (

           select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all

           select 1 union all select 1 union all select 1 union all select 1 union all select 1

           ),

           L1 (N) as (SELECT 1 FROM BaseNum bn1 CROSS JOIN BaseNum bn2),

           L2 (N) as (SELECT 1 FROM L1 a1 CROSS JOIN L1 a2),

           _Tally (N) as (

                  SELECT TOP(ISNULL(@EndValue - @StartValue + @Increment,@Rows)/@Increment)

                         rn= ISNULL(@StartValue,0) + ((ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1)*@Increment)

                  FROM L2 a1 CROSS JOIN L2 a2 CROSS JOIN L2 a3

                  )

    SELECT * FROM _Tally

    [/code]

    I have an unfinished "v.2." script which takes better care of spooling, and is also meant to work with odd combinations of input parameters: negatives, opposite signs, nulls here and there, etc.
    I'm still don't feel like I collected all the test cases. 
    πŸ™‚

  • o103452@rtrtr.com

    Ten Centuries

    Points: 1025

    Hi Sergiy, thanks for the script. Please keep us posted on your updated "V2" script. πŸ™‚

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

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