Create a Numbers Table in SQL Azure - SQL Azure Team Blog - Site Home - MSDN Blogs

Create a Numbers Table in SQL Azure

Create a Numbers Table in SQL Azure

Rate This
  • Comments 2

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.

  • I accept your challenge:

    DECLARE @N int = 1000000;

    DECLARE @FancyNumbers AS TABLE ( n int primary key );

    DECLARE @BoringNumbers AS TABLE ( n int primary key );

    SET STATISTICS TIME ON;

      WITH RecursiveRowGenerator (Row#, Iteration) AS (

           SELECT 1, 1

            UNION ALL

           SELECT Row# + Iteration, Iteration * 2

             FROM RecursiveRowGenerator

            WHERE Iteration * 2 < CEILING(SQRT(@N))

            UNION ALL

           SELECT Row# + (Iteration * 2), Iteration * 2

             FROM RecursiveRowGenerator

            WHERE Iteration * 2 < CEILING(SQRT(@N))

         )

         , SqrtNRows AS (

           SELECT *

             FROM RecursiveRowGenerator

            UNION ALL

           SELECT 0, 0

         )

    INSERT @FancyNumbers

    SELECT TOP(@N) 1 + A.Row# * POWER(2,CEILING(LOG(SQRT(@N))/LOG(2))) + B.Row# Row#

      FROM SqrtNRows A, SqrtNRows B

     ORDER BY A.Row#, B.Row#;

     WITH SimpleRecursiveGenerator (Row) AS (

          SELECT 1

           UNION ALL

          SELECT Row+1

            FROM SimpleRecursiveGenerator

           WHERE Row < @N

        )

    INSERT @BoringNumbers

    SELECT Row

     FROM SimpleRecursiveGenerator OPTION(maxrecursion 0);

    SET STATISTICS TIME OFF;

  • So, I guess the question is, why is my query faster even though it's clearly more complex and relies on doubly recursive CTEs and even a cross join? Is there anyone at the SQL Azure blog that could elaborate on what difficulties the query engine has with the simpler recursive CTE that mine solves?

Page 1 of 1 (2 items)
Leave a Comment
  • Please add 8 and 8 and type the answer here:
  • Post