14 November 2013

It’s all a numbers game

Just recently I have been using this small amount of code an awful lot so I thought I would share it here.

A lot has been written about using a tally or numbers table and I ‘m not about to re-write any of that. This is simply how to create one really quickly.

Lets start off small and create a table variable called digits:

Now a table is not a lot of use with out some records in it.

In my case I only want 10 rows in my table so the join to sysobjects might be a bit of overkill. The same could be achieved with

Whichever way it is done, we get a table with the numbers 0 – 9 in it.We can now use this to create a tally table of any size we desire with the code below.

This will insert every number from 0 to 9, 999 into our numbers table.If you need more rows simply add another CROSS JOIN to the digits table and add it ‘s [d] column to the calculation in the returned column with an appropriate multiplication factor. If you need a specific range of numbers then use a TOP clause to control it.

You are now ready to your Numbers table in any of the creative ways that they help someone working with TSQL improve their query performance, here is one example “The Numbers table and how it replaces a loop” – http: //www.sqlservercentral.com/articles/T-SQL/62867/

Keep up to date with Simple-Talk

For more articles like this delivered fortnightly, sign up to the Simple-Talk newsletter

This post has been viewed 2617 times – thanks for reading.

  • Rate
    [Total: 0    Average: 0/5]
  • Share

Jonathan Allen has been a SQL Server DBA since 1999, most enjoying performance tuning and development but also working with SSIS, SSRS to provide suitable business solutions. He is SQLSouthWest PASS Chapter Leader, blogs for Simple Talk, is a forum moderator at ask.sqlservercentral.com and is on Twitter. If you would like to find your nearest user group or just want to say hello then he would love to get an email from you.

View all articles by Jonathan Allen