Click here to monitor SSC

Jonathan has been working with SQL Server since 1999. He enjoys performance tuning, development and using SQL Server to provide appropriate business solutions. He is the founder and leader of the PASS SQL South West user group http://www.sqlsouthwest.co.uk , is a moderator at SQL Q + A forum ask.sqlservercentral.com and is on twitter at @fatherjack. He has spoken at SQLBits and SQL in the City, SQL Saturdays and local user groups across the UK and Europe.

It’s all a numbers game

Published 14 November 2013 7:39 pm

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

INSERT @digits

( 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)

INSERT Numbers

( [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/

 

 

One Response to “It’s all a numbers game”

Leave a Reply