The "Numbers" table

By: TripOtt

Posted on: September 5, 2012

I recently overheard the question at Blackbaud the other day, “What is the “Numbers” table used for?.  We’ll let me try to relay an answer to you good people.  The numbers table can be a pretty powerful thing to use.  Often times in SQL, you find yourself needing to generate numbers or finding gaps in numbered sequences.  You can use it to order things sequentially when querying for data.  For example, you can use them to help generate date/time in your queries:

DECLARE @STARTDATE as DATETIME = ‘1/1/2012′
DECLARE @ENDDATE as DATETIME  = ’12/31/2012’

select DATEADD(d,NUM,@STARTDATE) from NUMBERS where DATEADD(d,NUM,@STARTDATE) <= @ENDDATE

For more information on using a Numbers table see:

http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/you-require-a-numbers-table.aspx

http://www.simple-talk.com/sql/database-administration/creative-solutions-by-using-a-number-table/

Leave a Reply

Privacy Policy | Sitemap | © 2011 Blackbaud, Inc. All Rights Reserved

Digital Ocean