Create a Tally or Numbers Table

Question

Post reply

Create a Tally or Numbers Table

Jeff Moden

SSC Guru

Points: 1004378
More actions
March 12, 2008 at 1:15 am

#379172

Comments posted to this topic are about the item Create a Tally or Numbers Table
--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 - 1 through 15 (of 34 total)

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

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 1

Heh... I love it... someone gave me the ultra low mark of 1 star and didn't even have the nerve to tell me why.

Would it help if I told you that the method I used creates a million rows in about 5 seconds? :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)

Anipaul SSC-Insane Points: 24681 More actions · Answer 2

Hi Jeff

Your code was no 1 that's why the person gave you 1. Why are asking for explanation? :hehe:

Joke apart the article was fine. 🙂

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 3

Heh... that's funny... hadn't thought of it that way, AnirBan. 😀

I actually have an article coming out in the very near future as to "how and why" Tally tables work and and how then can be used to replace loops... the script here was just to make it easy for me to refer to when I post replies.

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

Anipaul SSC-Insane Points: 24681 More actions · Answer 4

Jeff Moden (4/29/2008)
Heh... that's funny... hadn't thought of it that way, AnirBan. 😀
I actually have an article coming out in the very near future as to "how and why" Tally tables work and and how then can be used to replace loops... the script here was just to make it easy for me to refer to when I post replies.

I will definitely wait for your article........:)

PaulR-122207 Ten Centuries Points: 1003 More actions · Answer 5

Jeff, just so you know, I gave you a 5 because I didn't give you a 1, 2, 3, or 4, and 5 is the maximum. I just want to make sure you know the reason for my rating! 😀

Kidding aside, I, too, will be awaiting your articles on usage of this table...

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 6

Thanks guys... I sure do appreciate your feedback.

I just got word that it's coming out on May the 7th.

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

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 7

But, it is a Tally table... it's used to count. And, besides... as you've already found out ol' friend... Tally is not likely to ever be a reserved word. "Sequence" has been a reserved word in things like Oracle for a long time... 😉

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

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 8

And you actually believe in the myth of code portability? That'll only happen when no one creates their own extensions to the ANSI standards. 😛

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

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 9

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

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.

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 10

Simple... Just like I said in the too short write up... I prefer the word "Tally" to "Number" 😀

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 11

Jeff Moden (4/30/2008)
Simple... Just like I said in the too short write up... I prefer the word "Tally" to "Number" 😀

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.

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

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 12

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.

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)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 13

Just google "tally table". The procedural (manual) form is used by schools a lot.

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 14

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?

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