Why should I consider using an auxiliary numbers table?

In response to a recent newsgroup question, I suggested a solution that would make use of a numbers table. The user responded that it would be a huge waste of space, since they shouldn't have to store every possible integer for this one query. 
 
I disagree. In that individual case (which I will not reproduce here), a query without a numbers table was much more verbose and possibly less efficient. 
 
Besides, there are many other reasons for using a numbers table. These examples will use the following table (which, for my purposes, will not need more than 1,024 rows, but you may need more): 
 

CREATE TABLE dbo.Numbers  (      Number INT IDENTITY(1,1) PRIMARY KEY CLUSTERED  )    WHILE COALESCE(SCOPE_IDENTITY(), 0) <= 1024  BEGIN      INSERT dbo.Numbers DEFAULT VALUES  END

 

---

 
Parsing a string 
 
Let's say you want to accept a comma-delimited list of integers, and you need to grab each value for part of your query. You can use dynamic SQL and an IN clause (but read this article first), you can loop through the list with a function (see Article #2248), or you can use a numbers table to derive the set of numbers from the list. The latter combines coding techniques safer than dynamic SQL and less complex than simulating an array.  
 

DECLARE @csv VARCHAR(255)  SET @csv = '1,3,5,7,9,14,36,395'    SELECT Number      FROM dbo.Numbers      WHERE CHARINDEX      (          ','+CONVERT(VARCHAR(12),Number)+',',          ','+@csv+','      ) > 0      ORDER BY Number

 
You can also do this with a list of strings, though the code is a little more complex. For example: 
 

DECLARE @csv VARCHAR(255)  SET @csv = 'every,good,boy,deserves,fudge'    SELECT word = SUBSTRING      (          ',' + @csv + ',',          Number + 1,      CHARINDEX          (              ',',              ',' + @csv + ',',               Number + 1          ) - Number - 1      )      FROM Numbers      WHERE Number >= 1          AND Number < LEN(',' + @csv + ',') - 1          AND SUBSTRING(',' + @csv + ',', Number, 1) = ','      ORDER BY Number        -- and if you want them sorted alphabetically      -- instead of the order they were listed,      -- change this to ORDER BY 1 or ORDER BY word

 

---

 
Finding IDENTITY gaps 
 
While in most circumstances you shouldn't care if your IDENTITY column has gaps, a numbers table can help identify any breaks in the sequence. 
 

CREATE TABLE dummy  (      ID INT IDENTITY(1,1)  )  INSERT dummy DEFAULT VALUES  INSERT dummy DEFAULT VALUES  INSERT dummy DEFAULT VALUES  INSERT dummy DEFAULT VALUES  INSERT dummy DEFAULT VALUES  INSERT dummy DEFAULT VALUES  INSERT dummy DEFAULT VALUES  DELETE dummy WHERE ID = 3  GO    SELECT Number      FROM dbo.Numbers      WHERE Number NOT IN (SELECT ID FROM dummy)      AND Number < (SELECT MAX(ID) FROM dummy)      ORDER BY Number        -- if you need only the first available      -- integer value, change the query to      -- SELECT MIN(Number) or TOP 1 Number    DROP TABLE dummy

 

---

 
Generating date ranges 
 
When you need to generate a set of dates in a range, the typical solution is to create a loop and iterate through the range, adding a day each time. However, a numbers table can help us generate this range as a set, instead of treating each date in the range individually. This way, you can use the code directly in a subquery or in a table-valued function, without having to worry about creating a temporary table to hold the values while you iterate through the loop. 
 

DECLARE @sDate SMALLDATETIME, @eDate SMALLDATETIME  SET @sDate = '20040101'  SET @eDate = '20040229'    SELECT @sDate + Number       FROM dbo.Numbers      WHERE @sDate + Number <= @eDate      ORDER BY 1

 
You could extend this example to create your own calendar table (which, like a numbers table, can be very useful—see Article #2519 for more information). The following example will create a calendar table with 1,024 consecutive days, starting at 2004-01-01 (based on the numbers table we created earlier). 
 

CREATE TABLE dbo.Calendar  (      dt SMALLDATETIME PRIMARY KEY CLUSTERED  )  GO    DECLARE @sDate SMALLDATETIME  SET @sDate = '20031231'  INSERT dbo.Calendar      SELECT @sDate + Number      FROM dbo.Numbers      ORDER BY Number

 

---

 
Generating time slices 
 
I often see requests for grouping statistics by 5-minute or 15-minute time intervals. A numbers table can help out with this greatly. To keep it distinct from a normal numbers table: 
 

CREATE TABLE dbo.timeIntervalDigits  (      n INT IDENTITY(1,1) PRIMARY KEY CLUSTERED  )  GO

 
Now, let's assume we're working on a 15-minute breakdown. We're going to populate this table with 96 rows (1-96), since there are 4 15-minute intervals per hour, and 24 hours per day. (If we were using 5-minute intervals, we'd have 288 rows.) 
 

SET NOCOUNT ON  INSERT timeIntervalDigits DEFAULT VALUES   WHILE SCOPE_IDENTITY() < 96  BEGIN       INSERT dbo.timeIntervalDigits DEFAULT VALUES   END  GO

 
So now, what is the relevant data we're looking to slice up? 
 

CREATE TABLE dbo.ArbitraryStats  (      dt SMALLDATETIME,      hitCount INT  )  GO    SET NOCOUNT ON    -- populate with a bunch of arbitrary data    DECLARE @dt SMALLDATETIME  SET @dt = CONVERT(CHAR(8), GETDATE(), 112)    INSERT ArbitraryStats SELECT @dt, 12  INSERT ArbitraryStats SELECT @dt, 15  INSERT ArbitraryStats SELECT DATEADD(MINUTE, 10, @dt), 12  INSERT ArbitraryStats SELECT DATEADD(MINUTE, 20, @dt), 10  INSERT ArbitraryStats SELECT DATEADD(MINUTE, 30, @dt), 12  INSERT ArbitraryStats SELECT DATEADD(MINUTE, 50, @dt), 12  INSERT ArbitraryStats SELECT DATEADD(HOUR, 1, @dt), 17  INSERT ArbitraryStats SELECT DATEADD(MINUTE, 150, @dt), 5  INSERT ArbitraryStats SELECT DATEADD(HOUR, 3, @dt), 1  INSERT ArbitraryStats SELECT DATEADD(HOUR, 4, @dt), 6  INSERT ArbitraryStats SELECT DATEADD(HOUR, 4, @dt), 7  INSERT ArbitraryStats SELECT DATEADD(HOUR, 6, @dt), 7

 
One way we can approach this is to use a derived subquery which holds the start and end time of each 15-minute slice, and then join that against our stats table. 
 

CREATE PROCEDURE dbo.getStatsBreakdown      @dt SMALLDATETIME = NULL  AS  BEGIN      SET NOCOUNT ON        -- if date is not passed in, use today      -- set to midnight in case time included        SET @dt = CONVERT(CHAR(8), COALESCE(@dt, GETDATE()), 112)        SELECT          n.StartTime,          n.EndTime,          HitCount = COALESCE(SUM(s.hitCount), 0)      FROM          ArbitraryStats s      RIGHT OUTER JOIN      (          SELECT              StartTime = DATEADD(MINUTE, 15 * (n-1), @dt),              EndTime = DATEADD(MINUTE, 15 * n, @dt)          FROM              timeIntervalDigits      ) n      ON          s.dt >= n.StartTime          AND s.dt < n.EndTime      WHERE          n.EndTime <= CURRENT_TIMESTAMP      GROUP BY          n.StartTime,          n.EndTime      ORDER BY          n.StartTime    END  GO

 
This procedure will take any date in the past. You can consider raising an error if the user enters a future date. Or, you can remove this limitation by commenting out the WHERE clause. 
 
If the date used is today's date, it will only generate stats for 15-minute periods that have been completed (including the current, in the odd case where you run the query exactly on a time slice boundary). 
 

---

 
Generating IP ranges 
 
Let's say you have an application that requires checking specific IP addresses against a range (say 128.11.0.x). You can use a numbers table to easily output all 256 possible values for the 4th octet, e.g. 
 

SELECT TOP 256 '128.11.0.'+CONVERT(VARCHAR(3), n-1)      FROM dbo.Numbers      ORDER BY Number

 

---

 
For some fun applications of a numbers table, see Vyas' site.