• <1 minute

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

[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.