I saw a someone post a question recently about solving a T-SQL problem. One of the answers given used a tally table, which the original poster didn’t understand. A few follow up links pointed them in the right direction, but it got me wondering.
How many of you know what a tally table is and how to create one? I bet a few of you don’t, which might mean you’ve never had the need. Or it might be a hole in your skill set and you didn’t realize that a tally table can be very useful in solving a number of problems. Anything from generating dates to splitting strings. There are plenty more ways to use one, and feel free to mention more in the comments that others might not have tried.
I’m not sure the tally table is a core T-SQL skill, but I think it’s an important one you should learn as you grow your skills. After you’ve mastered the basics (Insert/update/delete, aggregates, outer joins) Adding in an understanding of window functions, the APPLY operator, and CTEs are also important to allow you to become better at solving the problems you run into with more efficient T-SQL.