The Tally Table
-
dwain.c (8/25/2015)
Thank the Buddha for whoever came up with the idea of a Tally table in the first place! I just love 'em (but probably everybody knows that).BTW. Has anybody Googled "Tally Table" recently? Try hitting the I'm Feeling Lucky button.
For my next trick, I'll write an article "SEO for Dummies."
Tally Tables Rock!! This should be the official landing page for a yearly TallyTabFest.
I think the Chinese invented the concept thousands of years ago with their Abacus calculator.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
-
dwain.c (8/25/2015)
Thank the Buddha for whoever came up with the idea of a Tally table in the first place! I just love 'em (but probably everybody knows that).BTW. Has anybody Googled "Tally Table" recently? Try hitting the I'm Feeling Lucky button.
For my next trick, I'll write an article "SEO for Dummies."
You're so cool Dwain.
"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."-- Itzik Ben-Gan 2001
-
Jeff Moden (8/26/2015)
dwain.c (8/25/2015)
Thank the Buddha for whoever came up with the idea of a Tally table in the first place! I just love 'em (but probably everybody knows that).In my initial research, which seems a very long time ago, I found that some fellow in the '60s had the first mention of it and he also called it a Tally Table. I used to have a link about him but it stopped working about a year after I first found it.
Have you tried the Internet Archive/Wayback Machine? I was able to find some pretty obscure/long dead stuff there that I was looking for. I'd love to see that reference.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font] -
Wayne West (8/26/2015)
Jeff Moden (8/26/2015)
dwain.c (8/25/2015)
Thank the Buddha for whoever came up with the idea of a Tally table in the first place! I just love 'em (but probably everybody knows that).In my initial research, which seems a very long time ago, I found that some fellow in the '60s had the first mention of it and he also called it a Tally Table. I used to have a link about him but it stopped working about a year after I first found it.
Have you tried the Internet Archive/Wayback Machine? I was able to find some pretty obscure/long dead stuff there that I was looking for. I'd love to see that reference.
Yes. I even tried setting Bit 137 of DBCC TIMEWARP to no avail although I've not tried looking for it for that last couple of years. Another try seems to be in order.
--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 (8/26/2015)
Wayne West (8/26/2015)
Jeff Moden (8/26/2015)
dwain.c (8/25/2015)
Thank the Buddha for whoever came up with the idea of a Tally table in the first place! I just love 'em (but probably everybody knows that).In my initial research, which seems a very long time ago, I found that some fellow in the '60s had the first mention of it and he also called it a Tally Table. I used to have a link about him but it stopped working about a year after I first found it.
Have you tried the Internet Archive/Wayback Machine? I was able to find some pretty obscure/long dead stuff there that I was looking for. I'd love to see that reference.
Yes. I even tried setting Bit 137 of DBCC TIMEWARP to no avail although I've not tried looking for it for that last couple of years. Another try seems to be in order.
Did you set bit 137 or parameter 137? You know, some of those parameters are bit-wise, but I lost my documentation. I think someone else used it to get a hold of my copy. 😉
-
Ed Wagner (8/26/2015)
Jeff Moden (8/26/2015)
Wayne West (8/26/2015)
Jeff Moden (8/26/2015)
dwain.c (8/25/2015)
Thank the Buddha for whoever came up with the idea of a Tally table in the first place! I just love 'em (but probably everybody knows that).In my initial research, which seems a very long time ago, I found that some fellow in the '60s had the first mention of it and he also called it a Tally Table. I used to have a link about him but it stopped working about a year after I first found it.
Have you tried the Internet Archive/Wayback Machine? I was able to find some pretty obscure/long dead stuff there that I was looking for. I'd love to see that reference.
Yes. I even tried setting Bit 137 of DBCC TIMEWARP to no avail although I've not tried looking for it for that last couple of years. Another try seems to be in order.
Did you set bit 137 or parameter 137? You know, some of those parameters are bit-wise, but I lost my documentation. I think someone else used it to get a hold of my copy. 😉
I thought DBCC TIMEWARP was postponed until SQL 2025. Is that a CTP you're using? They can be awfully buggy in the more esoteric features.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font] -
Wayne West (8/26/2015)
Ed Wagner (8/26/2015)
Jeff Moden (8/26/2015)
Wayne West (8/26/2015)
Jeff Moden (8/26/2015)
dwain.c (8/25/2015)
Thank the Buddha for whoever came up with the idea of a Tally table in the first place! I just love 'em (but probably everybody knows that).In my initial research, which seems a very long time ago, I found that some fellow in the '60s had the first mention of it and he also called it a Tally Table. I used to have a link about him but it stopped working about a year after I first found it.
Have you tried the Internet Archive/Wayback Machine? I was able to find some pretty obscure/long dead stuff there that I was looking for. I'd love to see that reference.
Yes. I even tried setting Bit 137 of DBCC TIMEWARP to no avail although I've not tried looking for it for that last couple of years. Another try seems to be in order.
Did you set bit 137 or parameter 137? You know, some of those parameters are bit-wise, but I lost my documentation. I think someone else used it to get a hold of my copy. 😉
I thought DBCC TIMEWARP was postponed until SQL 2025. Is that a CTP you're using? They can be awfully buggy in the more esoteric features.
Nah... it's been an undocumented feature since 6.5 SP1. MS just doesn't want to support it so they leave it undocumented.
--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) -
Alan.B (8/26/2015)
dwain.c (8/25/2015)
Thank the Buddha for whoever came up with the idea of a Tally table in the first place! I just love 'em (but probably everybody knows that).BTW. Has anybody Googled "Tally Table" recently? Try hitting the I'm Feeling Lucky button.
For my next trick, I'll write an article "SEO for Dummies."
You're so cool Dwain.
Usually people just call me a nerd. I wish I know what made Google decide to rank that blog page so high so I could use the same trick on others.
Thanks Alan!
My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St -
I have still yet to come across a requirement that would benefit from the use of a tally table. I read up so much about it but that was so long ago I now wonder if I should reread all the articles again so I don't miss an opportunity through not remembering how it can be appropriate.
BTW Thanks for the example scenarios everyone as that may help me avoid missing an opportunity in the future!!!
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
-
To each, according to their needs. The only time that I've used a tally table was a project where I had to calculate performance based on the ticket -- was the ticket resolved in X days -- but I had to exclude weekends and holidays. So I used a tally table to build a table of 20-30 years of weekends and fixed date American holidays: 12/25, 1/1, 7/4, etc. with the understanding that the department manager knew that she had to add floating holiday dates (Thanksgiving, anything decided by upper management) every year. Whether or not they kept up with it after I left, I could care less.
I can't see using a tally table in my current project, but you never know when it might pop up. It's certainly a good thing to have a basic understanding of to keep in your tool box, just in case.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font] -
Gary Varga (3/4/2016)
I have still yet to come across a requirement that would benefit from the use of a tally table. I read up so much about it but that was so long ago I now wonder if I should reread all the articles again so I don't miss an opportunity through not remembering how it can be appropriate.BTW Thanks for the example scenarios everyone as that may help me avoid missing an opportunity in the future!!!
I use it often for test data or for splitting strings somehow. It's valuable there.
There are other uses, but those are where I find it useful.
-
Steve Jones - SSC Editor (3/4/2016)
Gary Varga (3/4/2016)
I have still yet to come across a requirement that would benefit from the use of a tally table. I read up so much about it but that was so long ago I now wonder if I should reread all the articles again so I don't miss an opportunity through not remembering how it can be appropriate.BTW Thanks for the example scenarios everyone as that may help me avoid missing an opportunity in the future!!!
I use it often for test data or for splitting strings somehow. It's valuable there.
There are other uses, but those are where I find it useful.
Test data? I think that I may be missing a trick there.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
-
Gary Varga (3/4/2016)
Steve Jones - SSC Editor (3/4/2016)
Gary Varga (3/4/2016)
I have still yet to come across a requirement that would benefit from the use of a tally table. I read up so much about it but that was so long ago I now wonder if I should reread all the articles again so I don't miss an opportunity through not remembering how it can be appropriate.BTW Thanks for the example scenarios everyone as that may help me avoid missing an opportunity in the future!!!
I use it often for test data or for splitting strings somehow. It's valuable there.
There are other uses, but those are where I find it useful.
Test data? I think that I may be missing a trick there.
For test data you might or might not use the columns of the tally table at all. That might be the important of these tables, help you realize that you're working with columns, but also with rows.
In this article[/url] I used something really similar to a tally cte to generate sample data, except I never used the numbers. It's still the same principle, but applied to something unrelated to the numbers.
Or maybe I'm missing Steve's way of generating test data?
-
Jeff Moden wrote a couple of articles in 2012 on generating test data using tally table techniques, but not an actual tally table.
Generating Test Data: Part 1 - Generating Random Integers and Floats
Generating Test Data: Part 2 - Generating Sequential and Random Dates
I stumbled upon them recently and put them in to my SSC briefcase.
(ninja'd by Steve *sigh* serves me right by trying for pretty formatting!) 😛
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
Viewing 15 posts - 61 through 75 (of 87 total)
You must be logged in to reply to this topic. Login to reply