Showing posts with label Linear Number. Show all posts
Showing posts with label Linear Number. Show all posts

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