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: //