Sequence number generation using RECURSIVE CTE LOOP in SQL Server

The below program will do so

DECLARE @maxLimit INT = 1000000

        ;WITH NumCTE AS(        
                        SELECT Rn = 1
                        UNION ALL
                        SELECT Rn+1 
                        FROM NumCTE WHERE Rn < @maxLimit)
        SELECT *
        FROM NumCTE 
        OPTION(MAXRECURSION 0)

In the above query, we are starting with number 1 and then in the recursive part the CTE moves on going till it reaches the @maxLimit. And at every step, the merging of the values is happening through the UNION ALL.

Finally the result is displayed outside the CTE.