Efficient Numbers Table

Validated against: SQL Server 2005.

Just performed a quick Google of the various methods of acquiring a list of sequential numbers and recommend the approach by Itzik Ben-Gan. The approach uses a small footprint of code and allows numbers up to 4,294,967,296. For details see URL:

CREATE FUNCTION dbo.GetNums(@n AS BIGINT) RETURNS TABLE
AS
RETURN
WITH
L0   AS(SELECT 1 AS c UNION ALL SELECT 1),
L1   AS(SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
L2   AS(SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
L3   AS(SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
L4   AS(SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
L5   AS(SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n FROM L5)
SELECT TOP (@n) n FROM Nums ORDER BY n;
GO
SELECT n FROM dbo.GetNums(1000)
This entry was posted in SQL 2005, SQL 2008, SQL 2008 R2 and tagged . Bookmark the permalink.