Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234»»

Create a Tally or Numbers Table Expand / Collapse
Author
Message
Posted Thursday, May 1, 2008 9:27 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:28 PM
Points: 35,263, Visits: 31,750
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #493707
Posted Thursday, May 1, 2008 9:31 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 19, 2014 7:50 PM
Points: 2,717, Visits: 3,854
You could call it an oilPrice table.

______________________________________________________________________

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
Post #493709
Posted Thursday, May 1, 2008 9:37 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, May 29, 2012 11:22 AM
Points: 1,755, Visits: 4,652
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...

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

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.
Post #493718
Posted Thursday, May 1, 2008 9:59 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 8:22 PM
Points: 7,074, Visits: 15,318
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...

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

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


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


----------------------------------------------------------------------------------
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?
Post #493738
Posted Thursday, May 1, 2008 10:49 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 11:09 PM
Points: 3,108, Visits: 11,502
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.



Post #493783
Posted Thursday, May 1, 2008 11:50 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:28 PM
Points: 35,263, Visits: 31,750
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... :P 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!


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

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #493813
Posted Thursday, May 1, 2008 4:34 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 8:24 PM
Points: 1,945, Visits: 3,017
No, no; you name a table for what it IS, not for how it is used. This is a sequence, which can be used for many things. I prefer to use a CTE of digits, followed by a CTE with the sum of (10^n * seq), since I can stick on to any query in schemas when I am not allowed to create tables. It is pretty fast and it is portable.

Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #493959
Posted Thursday, May 1, 2008 5:14 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:28 PM
Points: 35,263, Visits: 31,750
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.

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

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

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #493961
Posted Thursday, May 1, 2008 8:17 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,294, Visits: 9,480
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... :P 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!

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!" :D stand, do you think that you could give on your "No Plurals" stand for tables?
:)


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #494001
Posted Thursday, May 1, 2008 9:11 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:28 PM
Points: 35,263, Visits: 31,750
Heh... NO! :P

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

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #494022
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse