The Tally Table

Question

The Tally Table

Viewing 13 posts - 76 through 87 (of 87 total)

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

Ed Wagner SSC Guru Points: 287026 More actions · Answer 1

Wayne West (3/4/2016)
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!) 😛

He's also put together a SQL Saturday presentation on generating test data of various types for testing purposes.

Off the top of my head, there's string splitting, tearing apart a string to look at each byte to find the offensive character, generating test data, string cleaning, building tables of dates - really anything that deals with counting. I believe it was Gianluca Sartori who first called it "the Swiss Army Knife of SQL" years ago and I think it works. It's a flexible tool with many applications. Like any tool, it's there when you need it.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Gary Varga SSC Guru Points: 82166 More actions · Answer 2

Thanks all.

Gaz

-- Stop your grinnin' and drop your linen...they're everywhere!!!

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

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.

It's not just a Tally table... its the concept of creating a "presence of rows", even if you don't use anything from those rows, to replace loops. The Tally table is a physical manifestation of the abstract concept that has less direct use than the concept, which has a shedload of uses. I usually use the dbo.fnTally function I wrote instead of embedding Itzik Ben-Gans method not only as a Tally table but as a simple "presence of rows" to replace loops and rCTEs. The reason I use that function is 1) it's easy and 2) it consumes no reads. It's a bit slower than a physical Tally Table but the trade off for zero reads is well worth it.

--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: 1004409 More actions · Answer 4

I know this is an older thread but MS started making noises like they may add a built in Tally Function. Erland Sommarskog was the driving force there and they were asking him on the MVP email "forum". He came up with a couple of ideas but it didn't seem enough. Tally Table usage is almost like breathing for some folks. It just happens and you don't even think about it.

So, I wrote some of my uses and comments. Here's the comments and Erland actually added it to the ages old CONNECT item found at https://connect.microsoft.com/SQLServer/feedback/details/258733/add-a-built-in-table-of-numbers, which will be 10 years old this coming February. If you haven't already voted it up, take the time to add your vote.

Here is a further list, courtsey of Jeffery Moden:
Here are several things that I use it for.
1. Continue to use it for a splitter because the STRING_SPLIT function doesn't return element positions nor guarantee any order.
2. Luhn mod 10 checksum for credit card, bank routing, and other similar checksum algorithms.
3. Relational multiplication where rows must be replicated based on a value within the row.
4. Solving complex problems such as many different overlapping dates, currently active rows, distributions of values over multiple months, etc, etc.
5. Using it as a read-less "Pseudo-Cursor" (a row source with no SQL Level loop, every SELECT is a "Pseudo-Cursor") to generate millions of rows of test data in a very easy and intuitive manner in just seconds.
6. Many different string functions including numeric base translations, "initial caps", "data purification" functions such as returning all alpha or all numeric digit strings or removal of accidental control characters, unconventional splitters such as what I used to solve the conversion of million row Adjacency Lists to Nested Sets in less than a minute instead of days using the old push-stack method, removal of duplicated characters, special numeric checksum and encoding for GS1/Uuc/Ean-128 and other encoders/decoders, etc, etc, etc. There are several other non-delimited, character based places where performance would have suffered greatly had it not been for a Tally Table or Tally Function (numbers or sequence table or function for many of you).
That's certainly not the limit of what I used it for. I just can't remember all the places I've used it. It's one of those "imagination limited" tools where, to a good imagination, it's an unlimited tool. Like I said, I even use it as a simple "readless row source" to power some things that don't even need a counter but still needs to act as a high performance loop. Heh... and ask Itzik Ben-Gan about all the things he's used it for.
It would be really nice if it were a built in function that operated at machine language speeds, hopefully even faster than the "pseudo-cursors" that loop behind the scenes in every Insert, Select, Update, and Delete.

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

Gary Varga SSC Guru Points: 82166 More actions · Answer 5

I feel a bit deflated as I had already voted for this. I bet it was from an early suggestion from Jeff.

Gaz

-- Stop your grinnin' and drop your linen...they're everywhere!!!

Ed Wagner SSC Guru Points: 287026 More actions · Answer 6

I've already voted for it as well. As of now, it's 244 to 5.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

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

Gary Varga (1/9/2017)
I feel a bit deflated as I had already voted for this. I bet it was from an early suggestion from Jeff.

Thanks, Gary. Yes, it was a suggestion from about a year ago. This has to be the oldest active Connect item ever. 😉 At least it hasn't been closed as a "Won't fix".

--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: 1004409 More actions · Answer 8

Ed Wagner (1/9/2017)
I've already voted for it as well. As of now, it's 244 to 5.

You just have to wonder what was on the mind of the folks that voted it down.

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 9

Jeff Moden (1/9/2017)
Ed Wagner (1/9/2017)
I've already voted for it as well. As of now, it's 244 to 5.
You just have to wonder what was on the mind of the folks that voted it down.

I guess they didn't understand the concept of the tally table. They probably think is an alternative to "sequence".

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

DamianC SSCrazy Eights Points: 8023 More actions · Answer 10

Does 'voting is no longer enabled on this item' mean that a solution is imminent?

- Damian

Gary Varga SSC Guru Points: 82166 More actions · Answer 11

DamianC - Friday, January 20, 2017 4:30 AM
Does 'voting is no longer enabled on this item' mean that a solution is imminent?

I think it means that a response is imminent i.e. they may have made a decision. We may or may not like it though.

Gaz

-- Stop your grinnin' and drop your linen...they're everywhere!!!

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

Gary Varga - Friday, January 20, 2017 5:13 AM
DamianC - Friday, January 20, 2017 4:30 AM
Does 'voting is no longer enabled on this item' mean that a solution is imminent?
I think it means that a response is imminent i.e. they may have made a decision. We may or may not like it though.

It may mean that Microsoft has put a 10 year limit on an active suggestion. Considering the stupid stuff they've come out with over the years and how easy this would be to build into the product, you have to wonder what they're thinking. Have none of them ever used such a thing? Perhaps not. And, if you don't use it, you sometimes can't appreciate it.

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

Steve Jones - SSC Editor SSC Guru Points: 736494 More actions · Answer 13

I believe that this is considered to be "syntactic sugar" meaning why should they do this when it's easy to do in code.

I disagree, but I can only ask that someone resubmit this as a new request, maybe reword it and ask for a DMV that's a simple static table of sys.tally, single column (N), with a full list of bigint values.