Post reply

Create a Tally or Numbers Table

  • May 1, 2008 at 9:06 am

    #809645

    It's probably better to avoid using a reserved word, like INDEX, for a table name.

  • May 1, 2008 at 9:10 am

    #809649

    I prefer a table name of 'Numbers' or 'Integers' - and I like to use 'i' for the column name (for integer).

    It's all much of a muchness really.

    Ryan Randall

    Solutions are easy. Understanding the problem, now, that's the hard part.

  • May 1, 2008 at 9:12 am

    #809650

    Michael Valentine Jones (5/1/2008)

    It's probably better to avoid using a reserved word, like INDEX, for a table name.

    Or "number" or "sequence" or... that's all part of the reason why I use "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.

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

  • May 1, 2008 at 9:18 am

    #809654

    And, yeah... you could call it "Numbers"... but I'm one of those old guys where the table name shouldn't be a plural... :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.

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

  • May 1, 2008 at 9:27 am

    #809663

    RyanRandall (5/1/2008)

    IIt's all much of a muchness really.

    'zactly... 🙂

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

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

  • May 1, 2008 at 9:31 am

    #809665

    You could call it an oilPrice table. :hehe:

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg

  • May 1, 2008 at 9:37 am

    #809671

    Jeff Moden (5/1/2008)

    And, yeah... you could call it "Numbers"... but I'm one of those old guys where the table name shouldn't be a plural... :hehe:

    I knew I'd get that response (that's partly why I wrote it that way) 😀

    Again, I'm easy with either provided there's consistency within a defined scope.

    Ryan Randall

    Solutions are easy. Understanding the problem, now, that's the hard part.

  • May 1, 2008 at 9:59 am

    #809687

    RyanRandall (5/1/2008)

    Jeff Moden (5/1/2008)

    And, yeah... you could call it "Numbers"... but I'm one of those old guys where the table name shouldn't be a plural... :hehe:

    I knew I'd get that response (that's partly why I wrote it that way) 😀

    Again, I'm easy with either provided there's consistency within a defined scope.

    Ahh - but could we CALL the table "plural".....:w00t:

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • May 1, 2008 at 10:49 am

    #809725

    Jeff Moden (5/1/2008)

    Michael Valentine Jones (5/1/2008)

    It's probably better to avoid using a reserved word, like INDEX, for a table name.

    Or "number" or "sequence" or... that's all part of the reason why I use "Tally". 😉

    NUMBER is not a reserved word. Maybe you are thinking of NUMERIC?

    SEQUENCE is not a reserved word in 2005, but is listed as a future reserved word in 2005 BOL.

  • May 1, 2008 at 11:50 am

    #809747

    Michael Valentine Jones (5/1/2008)

    Jeff Moden (5/1/2008)

    Michael Valentine Jones (5/1/2008)

    It's probably better to avoid using a reserved word, like INDEX, for a table name.

    Or "number" or "sequence" or... that's all part of the reason why I use "Tally". 😉

    NUMBER is not a reserved word. Maybe you are thinking of NUMERIC?

    SEQUENCE is not a reserved word in 2005, but is listed as a future reserved word in 2005 BOL.

    Not in SQL Server it's not... 😛 You think SQL Server is the only place I use such a thing? Heh... most folks get on me because I don't really don't give a hoot about portability but the one place I do... wham! :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.

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

  • May 1, 2008 at 5:14 pm

    #809857

    Understood.

    You guys go ahead and call it whatever you want...that's the beauty of programming. I named it after the 2nd definition of "Tally" found here...

    http://education.yahoo.com/reference/dictionary/entry/tally

    ... which says...

    2.a. A stick on which notches are made to keep a count or score.

    ... stretching the definition a bit, it IS my computational "Tally" stick for reckoning what a loop would normally do. :hehe:

    Hey Joe... lot's of folks have made a "sequence" CTE such as what you say... would you mind posting yours? I've got one for Oracle (they call CTE's "Sub_Query Refactoring: in Oracle) at work that you might be interested in, as well. I'll post it tomorrow.

    In the meantime, if you have SQL Server 2005 and you want something that acts like a "Tally/Numbers/Sequence/Integers/IndexCount" table, here's a method that blows the doors off the recursive methods a lot of folks have come up with especially if it's called more than once... yes, I agree... it's non-portable code and I don't care... the Oracle method that I'll post tomorrow isn't either... I'm knowingly sacrificing portability for performance (like I usually do :P)...

    [font="Courier New"];WITH

    cteTally AS

    (--==== Create a Tally CTE from 1 to a desired count

     SELECT TOP (@DesiredCount)

            ROW_NUMBER() OVER (ORDER BY t1.Object_ID) AS N

       FROM Master.sys.All_Columns t1

      CROSS JOIN Master.sys.All_Columns t2

    )

     SELECT *

       FROM cteTally

    [/font]

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

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

  • May 1, 2008 at 8:17 pm

    #809892

    Jeff Moden (5/1/2008)

    Michael Valentine Jones (5/1/2008)

    Jeff Moden (5/1/2008)

    Michael Valentine Jones (5/1/2008)

    It's probably better to avoid using a reserved word, like INDEX, for a table name.

    Or "number" or "sequence" or... that's all part of the reason why I use "Tally". 😉

    NUMBER is not a reserved word. Maybe you are thinking of NUMERIC?

    SEQUENCE is not a reserved word in 2005, but is listed as a future reserved word in 2005 BOL.

    Not in SQL Server it's not... 😛 You think SQL Server is the only place I use such a thing? Heh... most folks get on me because I don't really don't give a hoot about portability but the one place I do... wham! :hehe:

    Ah, but "NUMBERS" is not a reserved word anywhere that I know of. Given that you've already caved on your "No Portability, Anywhere, Ever!" 😀 stand, do you think that you could give on your "No Plurals" stand for tables?

    🙂

    [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]

  • May 1, 2008 at 9:11 pm

    #809909

    Heh... NO! 😛

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

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

  • July 13, 2009 at 5:58 pm

    #1024187

    I prefer to use "Number" (one-based) so that I can distinguish it from "WholeNumber" which is zero-based. The column name I used for both tables is "value".

  • July 14, 2009 at 4:59 am

    #1024315

    I'd have a hard time doing that because Number is a reserved word but to each their own. Also, are you implying that you have two separate tables? One for one-based and the other for zero-based? If so and if the use of BETWEEN is a pain, why not just have a single table starting at zero and a pass-through view with a >0 criteria? The underlying clustered index would still be used in either case.

    Also, although I appreciate why you called your tables what you did and why, would it not be less confusing to casual users of your system to call one-based Number1 and zero-based Number0?

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

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

Viewing 15 posts - 16 through 30 (of 34 total)

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