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 Wednesday, April 30, 2008 7:49 PM
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: Friday, October 3, 2014 2:23 AM
Points: 3,108, Visits: 11,503
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) :D
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.








Post #493372
Posted Wednesday, April 30, 2008 8:52 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 1:53 PM
Points: 35,366, Visits: 31,905
Simple... Just like I said in the too short write up... I prefer the word "Tally" to "Number" :D


--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 #493385
Posted Wednesday, April 30, 2008 10:54 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 9:14 AM
Points: 9,294, Visits: 9,483
Jeff Moden (4/30/2008)
Simple... Just like I said in the too short write up... I prefer the word "Tally" to "Number" :D

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.


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #493412
Posted Thursday, May 1, 2008 6:41 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 1:53 PM
Points: 35,366, Visits: 31,905
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."

(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 #493520
Posted Thursday, May 1, 2008 8:55 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 9:14 AM
Points: 9,294, Visits: 9,483
Just google "tally table". The procedural (manual) form is used by schools a lot.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #493670
Posted Thursday, May 1, 2008 8:58 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 9:14 AM
Points: 9,294, Visits: 9,483
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?


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #493676
Posted Thursday, May 1, 2008 9:06 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: Friday, October 3, 2014 2:23 AM
Points: 3,108, Visits: 11,503
It's probably better to avoid using a reserved word, like INDEX, for a table name.


Post #493685
Posted Thursday, May 1, 2008 9:10 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, May 29, 2012 11:22 AM
Points: 1,755, Visits: 4,652
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.
Post #493690
Posted Thursday, May 1, 2008 9:12 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 1:53 PM
Points: 35,366, Visits: 31,905
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."

(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 #493691
Posted Thursday, May 1, 2008 9:18 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 1:53 PM
Points: 35,366, Visits: 31,905
And, yeah... you could call it "Numbers"... but I'm one of those old guys where the table name shouldn't be a plural...

--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 #493696
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse