Often we need to have a sequence of integer numbers in t-sql. There are lots of ways to generate integer sequence in t-sql. You can see one solution in the following location.

Integer sequence generator, Date sequence generator

In this post, we will see how easily we can generate integer sequence using CTE (common table expression). This solution is very short and easy to implement. But if we need a large number of integers, i.e. 1 million numbers, then this CTE based solution will be slow and will consume a lot of memory. If we need a small sequence, then probably this CTE based solution will be our choice.

Declare @start_number int, @end_number int

Select @start_number=3, @end_number=10;

With IntegerSequence( Number ) as -- Common table expression

(    

Select @start_number as Number

union all

Select Number + 1 

from IntegerSequence

where Number < @end_number

)

Select N.number

From IntegerSequence N

Option (MaxRecursion 0)

We will get the integer sequence as follows.

We can also create a table valued function using is CTE based solution. In that case, we can avoid using the  Option (MaxRecursion 0) every time and we will be able to use the table valued function everywhere to generate any integer sequence we need.

CREATE FUNCTION dbo.IntegerSequence (@start_number int, @end_number int )

 RETURNS @SequenceTable TABLE (sequence int)

AS

BEGIN

      ;With IntegerSequence( Number ) as -- Common table expression

      (    

            Select @start_number as Number

            union all

            Select Number + 1 

            from IntegerSequence

            where Number < @end_number

      )

      insert into @SequenceTable

      Select N.number

      From IntegerSequence N

      Option (MaxRecursion 0)

      return;

END;

GO

SELECT * from dbo.IntegerSequence (2, 1000);

SELECT * from dbo.IntegerSequence (200, 789);

This way we can use this table valued function anywhere and anytime we need to generate an integer sequence.