TSQL Solutions - Creating a table of sequential numbers

TSQL Solutions - Creating a table of sequential numbers

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 ;


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 ;


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 )


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 ;