Introduction
An SQL table of sequentially incrementing numbers can come in handy for solving several problems. It finds a role
in a variety of situations, right from parsing strings and generating dates to replacing complex procedural
constructs and cursors with simple and efficient set based code.
Quite often several complex procedural requirements can be broken down into simple SQL statements using a number
table. Regardless of how the table is created and the values are populated, they often come in handy. In general,
it is recommended that you create a permanent base table and insert as many numbers as you need for various
solutions. However for quick queries, formulating them as a part of the query can be equally effective.
Considerations
For permanent tables it does not matter how you create the numbers since you need to populate the table only
once. Always make sure the column is set as a Primary Key to avoid any potential duplication of rows.
In the following sections, a variety of approaches are detailed -- some require the existence of a permanent
table while several do not. The examples are written to show only numbers ranging from 1 to 500; however, using the
same logic one can create the sequence to any number depending on the requirements and physical limitations.
Also, in general, suggestions regarding efficiency and performance are often subjective. Regardless of how a
query is being used, the physical implementation determines the efficiency of a query. Therefore, rather than
relying on biased guidelines, it is imperative that you test the query and determine which one performs better.
Identity based sequences
Using the default values for an Identity Column in a table, one can simply insert default rows to generate
the sequence. One consideration in this approach is that it can be used only with permanent base tables. Note
that without any arguments IDENTITY property uses the value 1 for both seed and increment.
CREATE TABLE dbo.Nbrs(n INT NOT NULL IDENTITY) ;
GO
SET NOCOUNT ON ;
INSERT dbo.Nbrs DEFAULT VALUES ;
WHILE SCOPE_IDENTITY() < 500
INSERT dbo.Nbrs DEFAULT VALUES ;
With the IDENTITY() function one can use SELECT INTO a new table from any existing table. This example
demonstrates using a CROSS JOIN between two existing tables.
SELECT TOP 500 IDENTITY(INT) AS n
INTO dbo.Nbrs
FROM Northwind.dbo.Orders o1
CROSS JOIN Northwind.dbo.Orders o2 ;
Regular loops using WHILE
Here is a simple procedural loop with inserting one row at a time.
CREATE TABLE dbo.Nbrs(n INT NOT NULL PRIMARY KEY) ;
GO
SET NOCOUNT ON
DECLARE @Index INT ;
SET @Index = 1 ;
WHILE @Index <= 500 BEGIN
INSERT dbo.Nbrs (n) VALUES (@Index) ;
SET @Index = @Index + 1 ;
END
This is an efficient method of using WHILE loop from a newsgroup posting by Itzik Ben-Gen. The selection
of all the previously inserted rows from the same table reduces the number of iterations.
SET NOCOUNT ON
DECLARE @Index AS INT;
SET @Index = 1
INSERT dbo.Nbrs SELECT 1;
WHILE @Index * 2 < 500 BEGIN
INSERT dbo.Nbrs
SELECT @Index + n
FROM dbo.Nbrs
SET @Index = @Index + @@ROWCOUNT
END
INSERT dbo.Nbrs
SELECT @Index + n
FROM dbo.Nbrs
WHERE @Index + n <= 500
Sequences based on existing tables
Using an existing base table to view can be an easy way to generate a sequence of numbers. One drawback
to such mechanisms is that as the dataset gets larger, the comparative efficiency of the inserts can become
an issue.
Here is an example using a correlated subquery based on the unique column.
SELECT n
FROM ( SELECT ( SELECT COUNT(*)
FROM Northwind.dbo.Orders o2
WHERE o2.OrderId <= o1.OrderId ) AS "n"
FROM Northwind.dbo.Orders o1 ) Nbrs (n)
WHERE n <= 500 ;
This is the same logic as above, but using a self join
SELECT COUNT(*) AS "n"
FROM Northwind.dbo.Orders o1
INNER JOIN Northwind.dbo.Orders o2
ON o2.OrderId <= o1.OrderId
GROUP BY o1.OrderId
HAVING COUNT(*) <= 500 ;
Another quick way to generate the sequence is to use the ROW_NUMBER() function. This example uses a CROSS
JOIN just to illustrate the ease of formulation
WITH Nbrs ( n ) AS (
SELECT ROW_NUMBER() OVER (ORDER BY n) AS n
FROM ( SELECT s1.id
FROM Northwind.dbo.Orders o1
CROSS JOIN Northwind.dbo.Orders o2 ) D ( n )
) SELECT n
FROM Nbrs
WHERE n BETWEEN 1 AND 500 ;
Approaches using Common table expressions
Common table expressions are aliased resultsets derived from a simple query and defined within the
execution scope of a single DML statement. A CTE, with an alias, optional column list and a definition query,
can help simplify complex queries and improve readability. In general, any query can be written as a CTE
query, but there are certain instances where a query CTE construct can offer the best performing query.
Using a CTE, some of the above methods for generating a sequence of numbers can be simplified.
Here is a simple transformation of derived table of unit digits. The logic is pretty much self explanatory.
WITH Units ( nbr ) AS (
SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION
SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION
SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION
SELECT 9 )
SELECT u3.nbr * 100 + u2.nbr * 10 + u1.nbr + 1
FROM Units u1, Units u2, Units u3
WHERE u3.nbr * 100 + u2.nbr * 10 + u1.nbr + 1 <= 500 ;
Using recursive CTE
A Recursive CTE is common table expression can include references to itself. With an anchor member and a
recursive member, a recursive CTE can generate a number listing pretty efficiently.
WITH Nbrs ( n ) AS (
SELECT 1 UNION ALL
SELECT 1 + n FROM Nbrs WHERE n < 500 )
SELECT n FROM Nbrs
OPTION ( MAXRECURSION 500 )
Here is a clever method again by Itzik Ben-Gen using a CROSS JOIN of nested CTE references. This is not
only quick but can be used for generating larger datasets.
WITH Nbrs_3( n ) AS ( SELECT 1 UNION SELECT 0 ),
Nbrs_2( n ) AS ( SELECT 1 FROM Nbrs_3 n1 CROSS JOIN Nbrs_3 n2 ),
Nbrs_1( n ) AS ( SELECT 1 FROM Nbrs_2 n1 CROSS JOIN Nbrs_2 n2 ),
Nbrs_0( n ) AS ( SELECT 1 FROM Nbrs_1 n1 CROSS JOIN Nbrs_1 n2 ),
Nbrs ( n ) AS ( SELECT 1 FROM Nbrs_0 n1 CROSS JOIN Nbrs_0 n2 )
SELECT n
FROM ( SELECT ROW_NUMBER() OVER (ORDER BY n)
FROM Nbrs ) D ( n )
WHERE n <= 500 ;
With ROW_NUMBER()
For each row returned in a resultset, ROW_NUMBER function returns a sequential number, starting at 1. One can use any
existing table, view or any resultset to generate a ROW_NUMBER() expression. Here is an example:
SELECT n
FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY object_id )
FROM sys.objects ) D ( n )
WHERE n <= 500 ;
As a matter of fact, you can use any arbitrary expression or built-in functions like CURRENT_TIMESTAMP or NEW_ID() in the
ORDER BY clause to generate the sequential number. Here is an example re-written using a common table expression
WITH cte ( n ) AS (
SELECT ROW_NUMBER() OVER ( ORDER BY CURRENT_TIMESTAMP )
FROM Northwind.dbo.Orders )
SELECT * FROM cte
WHERE n <= 500 ;
Utilities
There are several areas where a table of sequentially incrementing numbers can come in handy. A very
common one is parsing a delimited string. Some examples can be found in the article
Faking arrays in Transact SQL
Another application is a calendar table that can be used for simplifying some complex temporal calculations.
For example, using a table of Numbers you can create a calendar table for the current year like:
SELECT dt, DATENAME( dw, dt )
FROM ( SELECT DATEADD( d, n - 1, CAST( CAST( YEAR( CURRENT_TIMESTAMP )
* 10000 + 101 AS VARCHAR(8) ) AS DATETIME ) )
FROM Nbrs ) D ( dt )
WHERE YEAR( dt ) = 2007 ;
The idea is to add the sequential numbers to a pre-specified start date and use a terminating condition using the WHERE clause
Identifying gaps or windows/"islands" in column values is another area where a table of sequential numbers can come in handy.
Conclusion
This article details some general ways of creating a table of sequentially incrementing numbers. As you
get familiar with the usage scenarios of using a number table, your elementary understanding of using
such shortcuts will improve. Naturally you will be able to appreciate such SQL techniques much better.
Acknowledgements
Mikhail Berlyant, Itzik Ben-Gan
|
