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.

Advertisements

3 thoughts on “SQL Tip: The Tally Table

  1. I know this article is more than a year old but, good Sir, thank you for the wonderful mention. I’m truly humbled in a good way.

    It’s also worth mentioning that I like your website. The light Blue is much less harsh than Black or White and the contrast of the Black lettering makes for an easy read. The code windows become easily obvious and the code they contain is very nicely formatted also making for an easy read.

    What really brought me here was a search on registered servers and CM. I found your good article at https://sqlbenjamin.wordpress.com/2018/08/07/sql-tip-multi-server-queries/ . I was impressed and thought I’d search your site for any mention of a Tally or Numbers table and here we are.

    Really nice job. Keep up the great work.

    Like

    1. Now you have me thinking I better go check all my posts for formatting. 😀 I recently had to move from TechNet to my own WordPress blog and in the migration some posts look a bit “off”. Anyway, thanks again for the comment!

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s