SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Create a Tally or Numbers Table


Create a Tally or Numbers Table

Author
Message
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)

Group: General Forum Members
Points: 218953 Visits: 42000
RyanRandall (5/1/2008)
IIt's all much of a muchness really.


'zactly... Smile

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jason Selburg
Jason Selburg
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7012 Visits: 4115
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 you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg
RyanRandall
RyanRandall
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4257 Visits: 4652
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) BigGrin

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.
Matt Miller (4)
Matt Miller (4)
SSC-Dedicated
SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)

Group: General Forum Members
Points: 30027 Visits: 19009
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) BigGrin

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?
Michael Valentine Jones
Michael Valentine Jones
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14813 Visits: 11848
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". Wink



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.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)

Group: General Forum Members
Points: 218953 Visits: 42000
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". Wink



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... Tongue 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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)

Group: General Forum Members
Points: 218953 Visits: 42000
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 Tongue)...

;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


--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
RBarryYoung
RBarryYoung
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35672 Visits: 9518
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". Wink



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... Tongue 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!" BigGrin stand, do you think that you could give on your "No Plurals" stand for tables?
Smile

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)

Group: General Forum Members
Points: 218953 Visits: 42000
Heh... NO! Tongue

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search