The Tally Table
-
Comments posted to this topic are about the item The Tally Table
-
I have used a tally table to help solve sudoku puzzles and to help run random batches among other things.
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 -
Also called a numbers table. In addition to the links you posted, Itzek Ben-Gan has writes about numbers tables in his books and articles.
Jeff Moden once said in a forum post to learn about tally tables and that doing so would change your career. That turned out to be true for me.
"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
-
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.
-
If knowing how to use a tally table is not a core skill, then perhaps it should be.
Thinking in sets is a skill in SQL that many times requires a paradigm shift in thinking, which can be difficult for people to make. In my experience, many programmers think like programmers, which usually means row by row. The earlier in your career the shift occurs, the easier it can be. After the basics, I think learning sets is essential if you want to progress your skill set.
I don't know Jeff's specific quote where he said it would change your career, but I have found it to be a true statement.
Gianluca once (years ago) called it the "Swiss Army Knife of SQL" and I think he was right.
The tally table can be used for so very many things that for me, it's almost ubiquitous to programming in SQL. I don't know what I'd do without one. Wait...yes I do. I would create one. 😛 We have them in a utility database on all our SQL Servers and it's accessible to everyone.
-
A tally table (or tally set because it's not always a table) is useful for those scenarios where you need to stub in gaps for a resultset. For example, the business wants a query that returns total daily sales for the past 30 days, including rows for those days on which there were no actual sales.
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.
Sometimes I use the following, but it contains only about 1,300 rows, which is usually enough. You get creative with the partitioning and windowing features of row_number() to solve a number of different problems.
select row_number() as tid over (order by object_id)from master.sys.columns;
This cross join version contains over 1.8 million rows for more demanding jobs.
select row_number() as tid over (order by a.object_id)from master.sys.columns a
cross join master.sys.columns b;
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
-
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
-
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.
-
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.
I agree that they don't get it, Luis. They referenced SEQUENCE, which supports the theory. I find it very hard to believe that they don't really get it and think that they're just playing dumb.
Honestly, I don't think it's new and shiny enough to be included as a "new" feature. They're focused on things like XML and JSON and coming up with inefficient methods of doing things instead of the tried and true techniques.
-
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.
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.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
-
Eric M Russell (8/13/2015)
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.
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?
-
Luis Cazares (8/13/2015)
Eric M Russell (8/13/2015)
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.
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?
Wear that T-shirt in public, and you'll be explaining a hundred times over to friends, family, and total strangers. 🙂
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
-
Eric M Russell (8/13/2015)
Luis Cazares (8/13/2015)
Eric M Russell (8/13/2015)
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.
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?
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 😀
-
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?
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:
-----
[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] -
I use the tally table for quite a few things. In fact I used one years ago without even knowing I was doing it. I can write a dynamic tally table from memory using cascading CTEs in about a minute.
They are handing and every DBA and Database Developer should learn about then early their career. For those that do, it will give them an edge against those who don't, because we all know that not everyone will take the time to learn these "advanced" techniques.
Viewing 15 posts - 1 through 15 (of 87 total)
You must be logged in to reply to this topic. Login to reply