Recursive CTE (Common Table Expression)

 In SQL Server, you can generate a sequence of numbers using various methods. One common approach is to use a CTE (Common Table Expression) with a recursive query or by leveraging a system table that contains a large number of rows. 

Here I am going to generate the sequence numbers from 1 to 50 (We can increase the number if needed).

Using Recursive CTE (Common Table Expression)

This method uses recursion to generate a sequence of numbers.

WITH NumberSequence AS 

    ( 

        SELECT 1 AS Number 

        UNION ALL 

        SELECT Number + 1 FROM NumberSequence WHERE Number < 50 

    ) 

SELECT Number FROM NumberSequence OPTION (MAXRECURSION 50);

CTE (Common Table Expression): The WITH clause defines a recursive CTE called NumberSequence.

The query starts with 1 and then recursively adds 1 until it reaches 50.

The OPTION (MAXRECURSION 50) limits the recursion to 50 iterations to avoid running into infinite loops.