The Dynamic Tally or Numbers Table

  • TheSQLGuru (10/5/2009)

    For me it was Hunt the Wumpus, hand-coded on my TRS-80 Model III circa 1979. I think there was another one I did, something that started with a 'Z' - Zork, Zon? BTW, that "computer" had 4K of RAM and CASSETTE TAPE DRIVE EXTERNAL STORAGE - and I was the baddest *** geek for a hundred miles (small-town NC at the time). Oh, did I mention that my parents forked over $999 for it?!?! Man have we come a damn long way!

    Wow - Zork!!! That takes me back!

    Oh and 4K RAM?! Luxury! The VIC had 3.5K...and any moment now someone is going to pop up who had a ZX-80 with its 1K RAM and 4K total ROM. Yep, the 80, not the 81. The one which had to blank the screen when it wasn't completely idle :w00t:

    I used to love nothing more than adjusting the azimuth of my tape heads to try to get it it 'turbo load' the latest game.

    Yes, we have come an awfully long way. Kids these days don't know they're born etc etc etc 😀

  • Give me a moment to whipe out a teardrop of nostalgia :alien:


    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • Have tried to run it (copy + paste) but get errors.

    What could I be doing wrong?

  • Peter Pirker (12/9/2009)

    Have tried to run it (copy + paste) but get errors.

    What could I be doing wrong?

    You may need to do some format fixing. The code provided originally was okay, but it appears that in preping for publication that some of the spaces have been removed.

  • Yes, I noticed - sorry to trouble you, and thank's for a great article.

  • Peter Pirker (12/9/2009)

    Yes, I noticed - sorry to trouble you, and thank's for a great article.

    Not a problem. Thanks for the compliment. I hope you are able to use the information provided.

  • the sqlist (9/22/2009)

    Here is another version that WILL work on any SQL version and the code is pretty much self descriptive and easy to understand by everyone. I agree that might not have ALL the features the code in article has but almost all. Still has the start/end/increment values

    Actually the start/end/increment values don't quite work because of this little code fragment


    number between @pStartValue and @pEndValue

    and number % @pIncrement = 0

    that last line should have been

    and (number - @pStartValue) % @pIncrement = 0

    so that it's possible to get, for example, a sequence like 5,8,11,14

    I haven't actually checked the code, just skim-read it, so I may have missed some other omissions.


  • Great article, Lynn. Extremely interesting, and good clear testing report and data.

    All those people querying why generate it dynamically amaze me - I wouldn't dream of having a static pre-generated 256 million row tally table in any system I was responsible for (but yes, I do have one with 11k rows, which is useful for many things) so dynamic generation is the only option if I ever need one that big. Also, from a performance point of view reading a big static Tally table is going to cause head movement on mty discs, whereas using a table valued function may enable me to avoid that IO overhead, so I have at least a chance that dynamic generation will reduce disc load compared to a static table.


  • Jeff Moden (10/2/2009)

    --===== Find the "gap ranges"

    -- This assumes that gaps include any whole number greater than 0

    SELECT GapStart = (SELECT ISNULL(MAX(b.MyID),0)+1

    FROM #MyTest b

    WHERE b.MyID < a.MyID),

    GapEnd = MyID - 1

    FROM #MyTest a


    It also doesn't need a mega-row tally table.

    Why do I think the last line of that code should read WHERE a.MyID NOT IN (SELECT MyID + 1 FROM #MyTest) and a.MyID > 1

    edit: I remember now why I hate html, at least with code sets other than unicode. It is something to do with "&" followed by "gt;" insteard of ">" and other similar inanities.


  • Just thought I'd try this, I noticed that the description says "The second CTE, named L1, uses the BaseNum CTE to generate a second result set with one thousand rows. It accomplishes this by crossing join BaseNum with itself two times." but the code only has one cross join so only produces 100 rows not 1,000.

Viewing 10 posts - 151 through 159 (of 159 total)

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