The Tally Table

Question

The Tally Table

Viewing 15 posts - 16 through 30 (of 87 total)

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

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

Wayne West (8/13/2015)
Luis Cazares (8/13/2015)
We need a T-shirt campaign to help drum up attention for MS Connect requests. Somebody like RedGate can put their logo on the back distribute them at events.
Something like this?
http://qa.sqlservercentral.com/Forums/Attachment17638.aspx
Wear that T-shirt in public, and you'll be explaining a hundred times over to friends, family, and total strangers. 🙂
Well, someone has to learn about tally tables 😀
Today, sir, you win the internet! Or at least SSC.
I'd buy that for a dollar! :hehe:

Luis, that looks like a winner. Put me down for one, please.

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

Steve Jones - SSC Editor SSC Guru Points: 728164 More actions · Answer 2

It's great that you love them, though I'd like to see you promote specific examples, not just sing the praises.

It's helpful for people that don't understand tally tables to see others in the world actually applying them in specific situations. In fact, more "example" articles would be welcome.

Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com

SQLRNNR SSC Guru Points: 281324 More actions · Answer 3

Luis Cazares (8/13/2015)
Ed Wagner (8/13/2015)
Eric M Russell (8/13/2015)
But where do you get your tally? There are examples out there; Jeff's is popular. However, it would be nice if SQL Server would provide an internal tally table just for the purpose, so we would always have it available in any environment, and it would perhaps have some optimization advantage over a physical table.
Erland Somerskog opened an MS Connect item for this back in 2007, but it hasn't been implemented. https://connect.microsoft.com/SQLServer/feedback/details/258733/add-a-built-in-table-of-numbers
Maybe the people in Microsoft should learn how and why to use a Tally Table as they don't seem to understand the concept. Or maybe they just don't care about it anymore.

It's not viewed as critical or essential. Many businesses have no idea what it is or how it would help so they put resources where it matters to businesses. Don't expect a Tally table to be implemented anytime soon, but be pleasantly surprised if it is.

Part of the reason could be that it is so easy for people to create their own table.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jack Corbett SSC Guru Points: 184388 More actions · Answer 4

When I'm involved in interviews I ask if they know about the tally/number table. I'm always surprised how many people have never heard about it. If they have I do follow up with how they would use it.

It is certainly a valuable tool.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Eric M Russell SSC Guru Points: 125255 More actions · Answer 5

Long overdue praise for the lowly tally table. I declare August 33rd as the official Tally Table Appreciation Day.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

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

Knut Boehnert (8/13/2015)
Knowing how and what to use a tally table for is not a core skill.
Working in sets of data instead of RBAR of data is a core skill.
The use of a tally table just shows that the person writing SQL (in whichever flavor) understands this core skill.

THAT is exactly correct! The Tally/Numbers table and its cousin, the Itzik Ben-Gan style cascading CTE (cCTE) are just one manifestation of the core skill of working in sets.

--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)
Intro to Tally Tables and Functions

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

Eric M Russell (8/13/2015)
Long overdue praise for the lowly tally table. I declare August 33rd as the official Tally Table Appreciation Day.

Ugh! That would mean that I'd have to change the Calendar table I told them we didn't need.

--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)
Intro to Tally Tables and Functions

Rod at work SSC-Dedicated Points: 33627 More actions · Answer 8

This is a skill that I do not have in my toolbox. I've heard of it, but haven't really investigated it much. You've got me curious Steve, thanks! I Binged it (hey, it's a decent search engine) and found a link to a blog post titled Tally Tables in T-SQL[/url]. Even that had something I'm not sure what the author is doing. They have the following code which they said will run on a SQL 2000 instance:

SELECT TOP 1000000 N=IDENTITY(INT, 1, 1)

INTO dbo.Numbers

FROM master.dbo.syscolumns a CROSS JOIN master.dbo.syscolumns b;

ALTER TABLE dbo.Numbers ADD CONSTRAINT NBR_pk PRIMARY KEY(N);

SELECT TOP 5 N FROM dbo.Numbers;

What's the purpose of doing a cross join between the master.dbo.syscolumns table, and then not using it? Sorry, inquiring minds want to know.

Kindest Regards, Rod Connect with me on LinkedIn.

g.britton SSChampion Points: 13862 More actions · Answer 9

I just wish it wasn't called a "Tally" table. I don't tally things, I count them. fwiw I usually call my "tally" table "N" to reflect it's relation ship to the set of natural numbers, usually represented by a double-struck N (now let's not argue if it should contain 0 (CS) or not (mathematics)).

Gerald Britton, Pluralsight courses

g.britton SSChampion Points: 13862 More actions · Answer 10

What's the purpose of doing a cross join between the master.dbo.syscolumns table, and then not using it? Sorry, inquiring minds want to know.

to get enough numbers to handle large applications

Gerald Britton, Pluralsight courses

Luis Cazares SSC Guru Points: 183686 More actions · Answer 11

Rod at work (8/14/2015)
What's the purpose of doing a cross join between the master.dbo.syscolumns table, and then not using it? Sorry, inquiring minds want to know.

Well, the point is that you are actually using it. You're not using the columns, but you're using the rows. You need to generate the rows somehow and using large tables is easier than creating the rows on the fly like this:

SELECT N=IDENTITY(INT, 1, 1)

INTO dbo.Numbers

FROM (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1) a(n)