my favorite method is Itzik Ben-Gan's auxiliary table of numbers. In case you cannot, or do not want to generate a permanent table, you can produce a virtual one on the fly using this method very efficiently.
DECLARE @max_rows AS BIGINT;
SET @max_rows = 50;-- put here any number you want
WITH
lv0 AS (SELECT 0 AS g UNION ALL SELECT 0),
lv1 AS (SELECT 0 AS g FROM lv0 AS a CROSS JOIN lv0 AS b), -- 4
lv2 AS (SELECT 0 AS g FROM lv1 AS a CROSS JOIN lv1 AS b), -- 16
lv3 AS (SELECT 0 AS g FROM lv2 AS a CROSS JOIN lv2 AS b), -- 256
lv4 AS (SELECT 0 AS g FROM lv3 AS a CROSS JOIN lv3 AS b), -- 65,536
lv5 AS (SELECT 0 AS g FROM lv4 AS a CROSS JOIN lv4 AS b), -- 4,294,967,296
Nums AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n FROM lv5)
SELECT TOP (@max_rows) n FROM Nums ORDER BY n;when you wrap this in to a Table Valued Function you can call it whenever you want to generate sequential numbers.
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE FUNCTION GenerateSqnNumbers
(
@max_rows int
)
RETURNS TABLE
AS
RETURN
(
WITH
lv0 AS (SELECT 0 AS g UNION ALL SELECT 0),
lv1 AS (SELECT 0 AS g FROM lv0 AS a CROSS JOIN lv0 AS b),
lv2 AS (SELECT 0 AS g FROM lv1 AS a CROSS JOIN lv1 AS b),
lv3 AS (SELECT 0 AS g FROM lv2 AS a CROSS JOIN lv2 AS b),
lv4 AS (SELECT 0 AS g FROM lv3 AS a CROSS JOIN lv3 AS b),
lv5 AS (SELECT 0 AS g FROM lv4 AS a CROSS JOIN lv4 AS b),
Nums AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n FROM lv5)
SELECT TOP (@max_rows) n FROM Nums ORDER BY n
)
GO Other Methods
Using another recursive Common Table Expression.WITH cte AS
(
SELECT 1 AS num
UNION ALL
SELECT (num + 1) AS num FROM cte
WHERE num < @SomeMaximum
)
SELECT * FROM cte OPTION (MAXRECURSION 0); Note that without the MAXRECURSION option CTE recursion depth in MS SQL is limited to 100. (value of 0 disables the recursion limit)
Using While loop
DECLARE @tmp_numbTable AS TABLE (Number BIGINT);
DECLARE @MaxSqnNumber BIGINT = 1000;
DECLARE @MinSqnNumber BIGINT = 0;
WHILE @MaxSqnNumber >@MinSqnNumber
BEGIN
SET @MinSqnNumber = @MinSqnNumber + 1
INSERT @tmp_numbTable ( [Number] ) VALUES(@MinSqnNumber)
PRINT @MinSqnNumber
END
SELECT * FROM @tmp_numbTable AS tnt
