SQL Tip: The Tally Table

This is going to be a short blog because there are far better articles on the Tally Table that you should read (I’ll provide a link or two). However, I felt it important to create a post on this since some of my scripts make use of just such a table.

The tally table is just a table that has a column with sequential numbers, hence it is also sometimes referred to as a numbers table. It’s a handy table because you can use it to take the place of loops and perform operations in sets rather than loops to see performance improvements. There’s a lot of ways to use it and as you do you’ll realize just how great and powerful it can be.

The most common way I use it is when I create time periods or time ‘spans’. You can use the numbers in the table to add hours/days/weeks/etc to a starting date and then easily use those dates/times in the query. Since I’m not adding an example of my own in this post it may not make as much sense until you read the article I’m going to share or go through one of my posts that makes use of the table.

I think the best article on the subject is from Jeff Moden. In fact, I still use his ‘create and populate a tally table’ script almost entirely as he originally wrote it in my creation scripts. So, if you’re new to the concept, have a read of his article here: http://www.sqlservercentral.com/articles/T-SQL/62867/

I create a database on each of my SQL servers named “DBA” where I store stored procedures, views, etc for performing maintenance or other DBA tasks. Each DBA database also has a “TallyTable” created with 100,000 records and I allow read access to public so that anyone can use the table. If you’re unable to create such a table then you can create one as a CTE or a temp table for your scripts – it may not be as ideal but at least it will work. If you’re trying to use one of my scripts that makes use of the TallyTable and you can’t create one use the one of the following in the script and replace the table references.

CTE Example:

;WITH TallyTable AS (
SELECT TOP 100000 ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS [N]
  FROM dbo.syscolumns tb1,dbo.syscolumns tb2 -- or you could use a large table from your ConfigMgr db if necessary
)
SELECT * FROM TallyTable;

Temp Table Example:

SELECT TOP 100000 ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS [N]
  INTO #TallyTable
  FROM dbo.syscolumns tb1,dbo.syscolumns tb2; -- or you could use a large table from your ConfigMgr db if necessary
SELECT * FROM #TallyTable;

I know I didn’t do a whole lot more than point you to someone else’s article on this powerful trick, but I didn’t want to try and rewrite something that someone has already written…many many years ago. Plus, as you see how I use it in my scripts you’ll get real world examples of how to apply/use it. Now, go and read Jeff’s excellent write up and then do a search on the web for other articles – but I’d start with his.