Create a Numbers Table in SQL Azure

[This article was contributed by the SQL Azure team.]

Often you may require number table for various purposes like parsing a CSV file into a table, string manipulation, or finding missing identities (see example below), etc. Numbers tables are used to increase performance (by avoiding cursors) and simplify queries. Because SQL Azure requires all tables have clustered indexes, you’ll need to things slightly differently that you would with SQL Server or SQL Express (see the blog post entitled SELECT INTO With SQL Azure). Here is a short Transact-SQL script that will create a numbers table in SQL Azure.

The Script:

 SET NOCOUNT ON

CREATE TABLE Numbers (n bigint PRIMARY KEY)

GO

DECLARE @numbers table(number int);
WITH numbers(number) as
(
 SELECT 1 AS number
 UNION all
 SELECT number+1 FROM numbers WHERE number<10000
)
INSERT INTO @numbers(number)
SELECT number FROM numbers OPTION(maxrecursion 10000)

INSERT INTO Numbers(n)
SELECT number FROM @numbers

You can easily do the same thing for date ranges. The script looks like this:

 SET NOCOUNT ON

CREATE TABLE Dates (n datetime PRIMARY KEY)

GO

DECLARE @dates table([date] datetime);
WITH dates([date]) as
(
 SELECT CONVERT(datetime,'10/4/1971') AS [date]
 UNION all
 SELECT DATEADD(d, 1, [date]) FROM dates WHERE [date] < '10/3/2060'
)
INSERT INTO @dates(date)
SELECT date FROM dates OPTION(maxrecursion 32507)

INSERT INTO Dates(n)
SELECT [date] FROM @dates

Now that you have created a numbers table you can use it to find identity gaps in a primary key, here is an example:

 -- Example Table
CREATE TABLE Incomplete 
( 
    ID INT IDENTITY(1,1),
    CONSTRAINT [PK_Source] PRIMARY KEY CLUSTERED ( [ID] ASC)
)


-- Fill It With Data
INSERT Incomplete DEFAULT VALUES 
INSERT Incomplete DEFAULT VALUES 
INSERT Incomplete DEFAULT VALUES 
INSERT Incomplete DEFAULT VALUES 
INSERT Incomplete DEFAULT VALUES 
INSERT Incomplete DEFAULT VALUES 

-- Remove A Random Row
DELETE Incomplete WHERE ID = (SELECT TOP 1 ID FROM Incomplete ORDER BY NEWID())

GO 

-- Find Out What That Row Is 
SELECT n 
    FROM dbo.Numbers 
    WHERE n NOT IN (SELECT ID FROM Incomplete) 
    AND n < (SELECT MAX(ID) FROM Incomplete) 
    ORDER BY n 
 
    -- if you need only the first available 
    -- integer value, change the query to 
    -- SELECT MIN(Number) or TOP 1 Number 
 
-- Clean Up The Example Table
DROP TABLE Incomplete

Summary

Do you have a better way to accomplish the same thing in SQL Azure? Post it in the comments below; make sure to test it on SQL Azure first. Do you have questions, concerns, comments? Post them below and we will try to address them.