Sequence Generator

CREATE FUNCTION dbo.fnSequenceGenerator  (@Limit INT)

RETURNS @Sequence TABLE(SequentialNumber INT)

AS

  BEGIN

    DECLARE  @RunningValue INT = 1

    WHILE @RunningValue <= @Limit

      BEGIN

        INSERT @Sequence

        VALUES(@RunningValue)

        SET @RunningValue += 1

      END

    RETURN

  END

GO               

-- Test

SELECT * FROM   dbo.fnSequenceGenerator(11)

GO

-- Method 2:

-- SQL cte - Common Table Expression :SQL sequence

-- Create a cte to give the sequence of 0-9

with cteDigits as

(    

select 0 as Digit

union select 1

union select 2

union select 3

union select 4

union select 5

union select 6

union select 7

union select 8

union select 9),

              

-- Create a second cte to generate a million number sequence

cteMillion as (

select SeqNo=hundredT.Digit * 100000+tenT.Digit * 10000 +

       thousands.Digit *1000 + hundreds.Digit * 100 +

       tens.Digit * 10 + ones.Digit + 1

from cteDigits as ones

-- SQL cross join

cross join cteDigits as tens

cross join cteDigits as hundreds

cross join cteDigits as thousands

cross join cteDigits as tenT

cross join cteDigits as hundredT ) 

-- Main query

-- SQL select from cte

select top 1000 SeqNo

from cteMillion

order by SeqNo

go