Wednesday, April 24, 2013

Generate Incremented Linear Number Sequence

There are lots of methods of creating numbers in SQL, We could use a recursive Common Table Expression or we could use a cross join of sys.columns (or any other system table or view) with itself for lots of numbers, or simply generate a numbers table.
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