# Create a Tally or Numbers Table

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

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

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

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

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

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

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

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

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

• 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?

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

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

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

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

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

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

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

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

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

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