Create a Tally or Numbers Table

  • Comments posted to this topic are about the item Create a Tally or Numbers Table

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

  • Heh... I love it... someone gave me the ultra low mark of 1 star and didn't even have the nerve to tell me why.

    Would it help if I told you that the method I used creates a million rows in about 5 seconds? :hehe:

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

  • Hi Jeff

    Your code was no 1 that's why the person gave you 1. Why are asking for explanation? :hehe:

    Joke apart the article was fine. 🙂

  • Heh... that's funny... hadn't thought of it that way, AnirBan. 😀

    I actually have an article coming out in the very near future as to "how and why" Tally tables work and and how then can be used to replace loops... the script here was just to make it easy for me to refer to when I post replies.

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

  • Jeff Moden (4/29/2008)


    Heh... that's funny... hadn't thought of it that way, AnirBan. 😀

    I actually have an article coming out in the very near future as to "how and why" Tally tables work and and how then can be used to replace loops... the script here was just to make it easy for me to refer to when I post replies.

    I will definitely wait for your article........:)

  • Jeff, just so you know, I gave you a 5 because I didn't give you a 1, 2, 3, or 4, and 5 is the maximum. I just want to make sure you know the reason for my rating! 😀

    Kidding aside, I, too, will be awaiting your articles on usage of this table...

  • Thanks guys... I sure do appreciate your feedback.

    I just got word that it's coming out on May the 7th.

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

  • But, it is a Tally table... it's used to count. And, besides... as you've already found out ol' friend... Tally is not likely to ever be a reserved word. "Sequence" has been a reserved word in things like Oracle for a long time... 😉

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

  • And you actually believe in the myth of code portability? That'll only happen when no one creates their own extensions to the ANSI standards. 😛

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

  • Why not just call it a NUMBER table, since that's what it contains? Well, it's really integers, but INTEGER is a reserved word...

    (Shameless plug for my own Number Table Function) 😀

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685

    You may find uses for the F_TABLE_NUMBER_RANGE function in situations where you need a lot of numbers, or you just want to have a custom sequence of numbers, like -3333 through 437,283. Or you can just use it to load your permanent number/tally table.

  • Simple... Just like I said in the too short write up... I prefer the word "Tally" to "Number" 😀

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

  • Jeff Moden (4/30/2008)


    Simple... Just like I said in the too short write up... I prefer the word "Tally" to "Number" 😀

    The only problem is, there already is something called a "tally table" that is completely different, but similar enough to be confusing: a tally table is a programming (or procedural) construct used to accumulate aggregate statistics, for instance for a "Select grp, count(*) From Foo Group By grp" type statement.

    It's a cool name, but I strongly prefer clear distinction in my nomenclature.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • Thanks, Barry... I've never heard of anyone else calling such aggregations a "Tally Table", but I certainly can see where the term both applies and is convenient.

    Just to throw another name into the mix, I've heard some people call what I'm calling a "Tally" table, an "Index" table, which also seems appropriate considering its use. They even used the letter "i" for the column name instead of "N".

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

  • Just google "tally table". The procedural (manual) form is used by schools a lot.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • Jeff Moden (5/1/2008)


    Just to throw another name into the mix, I've heard some people call what I'm calling a "Tally" table, an "Index" table, which also seems appropriate considering its use. They even used the letter "i" for the column name instead of "N".

    "Index table" does make sense, but the reuse of "Index" would probably be confusing to SQL practioners. Maybe "Indexing Table" would work?

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

Viewing 15 posts - 1 through 15 (of 35 total)

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