Virtual tally table function

  • Steven Willis

    SSCrazy Eights

    Points: 9893

    Comments posted to this topic are about the item Virtual tally table function

  • Steven Willis

    SSCrazy Eights

    Points: 9893

    While developing this function several alternative versions were suggested and tested. Each version seems to have its pros and cons. The posted version limits the lower-bound to > 0. To view the alternative versions, testing methodology and test results see the attached file which has all the necessary scripts for anyone who wants to jump in and offer suggestions.

     

  • craig buckham

    SSC Enthusiast

    Points: 129

    Here is a slightly altered version that will print results between (and including) any two integers, forward or backward. I also included the row number, since it is often useful.

    create FUNCTION [dbo].[TallyFromTo]

    (

    @start BIGINT

    ,@stop BIGINT

    )

    RETURNS TABLE

    WITH SCHEMABINDING

    AS

    RETURN

    /* Uses the Bates algorithm for generating the rows from */

    /* http://www.sqlservercentral.com/scripts/Tally+Table/99617/ */

    /*

    Usage:

    SELECT N FROM [dbo].[TallyFromTo](1001,1100)

    SELECT N FROM [dbo].[TallyFromTo](-10,100)

    SELECT N FROM [dbo].[TallyFromTo](0,1000)

    SELECT N FROM [dbo].[TallyFromTo](3,-20)

    SELECT N FROM [dbo].[TallyFromTo](-1,-1)

    SELECT N FROM [dbo].[TallyFromTo](1001,1000)

    SELECT N FROM [dbo].[TallyFromTo](1,1000)

    */

    WITH parms as (select SIGN(@stop-@start) as "sgn"),

    T1(F) 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

    ),

    T2(F) AS

    (SELECT 1 FROM T1 A, T1 B),

    T3(F) AS

    (SELECT 1 FROM T2 A, T2 B),

    T4(F) AS

    (SELECT 1 FROM T3 A, T3 B),

    cteTally(RN) AS

    (

    SELECT TOP (ABS(@stop-@start)+1)

    ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

    FROM T4

    )

    SELECT

    RN as "RowN", p.sgn*RN+@start-p.sgn as "N"

    FROM

    cteTally T cross join parms p

    ;

  • Steven Willis

    SSCrazy Eights

    Points: 9893

    Thanks for the feedback!

    I haven't had a chance to try your version yet, but I'll run it through my test script when I get time and will post my results.

    This will be heresy to some, but sometimes getting to a specific unique goal (such as including negative numbers or adding an additional column) at the cost of some performance may be necessary!

  • Dwain Camps

    SSC Guru

    Points: 86893

    Steven - Hey dude, thanks for the credit even though I'm sure my contribution was minimal.

    Didn't notice it on publication because I was watching for an article and besides I've just returned from an extended visit to Papua New Guinea where my favorite SQL application just went live.

    Tally tables rock!


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • Jeff Moden

    SSC Guru

    Points: 997128

    Speaking of rocks, I feel like a rock because I totally missed this script entry. Thanks for the kudos, Steven, but I can't claim this method as my original thought. Itzik Ben-Gan was the first person I know of that came up with this and I've just been the one to push the idea of Tally Tables/CTEs to have them become household words.

    It's really good to see that so many people have taken up the banner and are creating wonderful permutations of their own.

    Shifting gears, it's also wonderful to see people doing "million row testing" to come up with actual performance data instead of merely speculating what works better based on code content or "in my experience" claims like it used to be in the early days. Well done!

    And, one more time...

    /* Uses the Bates algorithm for generating the rows from */ /* http://www.sqlservercentral.com/scripts/Tally+Table/99617/ */

    Although I certainly applaud Mr. Bates for his efforts, Itzik published the TOP optimization years before Mr. Bates did. I'm not sure I can quickly find the article that Itzik published but I'll look for it. There's also an optimization I came up with for starting such a thing at "0" instead of subtracting 1 from each generated value. I'll try to remember to post that after work, as well.

    --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)

  • Steven Willis

    SSCrazy Eights

    Points: 9893

    Thanks Jeff. Any improvements (performance tested of course!) will be integrated and all ideas are welcome.

    Concerning credits...I'll go ahead and make sure the proper credit is applied. There's plenty to go around and I just want people to know I'm just a "tool builder" and not the originator of the base methods.

    As far as comment on the Virtual Tally Table function itself: there seems to be more variations of input desired than the base function offers. The range of numbers, for example, could be base 0 or 1, or could allow negative numbers, or have an enforced upper-limit, etc. I think I'll add some additional parameters, but will have to see how that affects performance. I look forward to any ideas for extending input options.

     

  • craig buckham

    SSC Enthusiast

    Points: 129

    The TallyFromTo listing posted above on 7/25/2013 does allow for negative numbers, and also allows a choice of direction. I did not check the timing, but I guarantee it is fast enough to be used any time a tally function is needed.

  • Steven Willis

    SSCrazy Eights

    Points: 9893

    I'll make a list of possible changes for review so we can all contribute and not have to do stuff over and over. Thanks.

    1 Negative numbers

    2 Base 0 or 1

    3 Number direction

    4 ?

     

  • Dwain Camps

    SSC Guru

    Points: 86893

    Jeff Moden (8/15/2013)


    Speaking of rocks, I feel like a rock because I totally missed this script entry. Thanks for the kudos, Steven, but I can't claim this method as my original thought. Itzik Ben-Gan was the first person I know of that came up with this and I've just been the one to push the idea of Tally Tables/CTEs to have them become household words.

    An idea is but an opportunity without promotion. Good marketing gets those ideas out to the world and leads to adoption.

    Look at Ginsu Knives. Without Ronco, it would have been a mere blip on the historical radar, but I'm sure everyone knows what it is, and yes I have a set I bought more than 30 years ago.

    I consider you the chief-marketeer, or more appropriately most visible proponent of the tally table as a tool. Inventor and proponents each have their place in history. The question is whose contribution will be better remembered when the history books are written.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

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

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