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:
DECLAREÂ @digitsÂ TABLEÂ (Â dÂ INTÂ )
Â Now a table is not a lot of use with out some records in it.
INSERTÂ Â @digits
Â Â Â Â Â Â Â Â (Â [d]Â )
Â Â Â Â Â Â Â Â SELECTÂ TOPÂ 10
Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â ROW_NUMBER()Â OVERÂ (Â ORDERÂ BYÂ idÂ )Â -Â 1
Â Â Â Â Â Â Â Â FROMÂ Â Â Â [sys].[sysobjects]Â ASÂ S
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
( d )
VALUES ( 0 ), ( 1 ), ( 2 ), ( 3 ), ( 4 ), ( 5 ), ( 6 ), ( 7 ), ( 8 ), ( 9 )
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.
CREATE TABLE NumbersÂ (N INT)
( [N] )
SELECT D.[d] + [D2].[d] * 10 + [D3].[d] * 100 + [D4].[d] * 1000
FROM @digits AS D ,Â @digits AS D2 ,Â @digits AS D3 ,Â @digits AS D4
Â 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/