In SQL Server, we can use the sp_sequence_get_range stored procedure to generate a range of sequence numbers from a sequence object.

Syntax

The syntax for the sp_sequence_get_range stored procedure goes like this:

sp_sequence_get_range [ @sequence_name = ] N'<sequence>'   
     , [ @range_size = ] range_size  
     , [ @range_first_value = ] range_first_value OUTPUT   
    [, [ @range_last_value = ] range_last_value OUTPUT ]  
    [, [ @range_cycle_count = ] range_cycle_count OUTPUT ]  
    [, [ @sequence_increment = ] sequence_increment OUTPUT ]  
    [, [ @sequence_min_value = ] sequence_min_value OUTPUT ]  
    [, [ @sequence_max_value = ] sequence_max_value OUTPUT ]  
    [ ; ]

Example

Suppose we create a sequence like this:

CREATE SEQUENCE Sequence1
    START WITH 1
    INCREMENT BY 1
    MINVALUE 1
    MAXVALUE 1000
    CYCLE;

We can now use the sp_sequence_get_range stored procedure to return a range of sequence values from that object, as well as metadata related to the range:

DECLARE
    @FirstValue sql_variant,
    @LastValue sql_variant,
    @CycleCount int,
    @Incr sql_variant,
    @MinValue sql_variant,
    @MaxValue sql_variant;

EXEC sys.sp_sequence_get_range
    @sequence_name = N'dbo.Sequence1',
    @range_size = 600,
    @range_first_value = @FirstValue OUTPUT,
    @range_last_value = @LastValue OUTPUT,
    @range_cycle_count = @CycleCount OUTPUT,
    @sequence_increment = @Incr OUTPUT,
    @sequence_min_value = @MinValue OUTPUT,
    @sequence_max_value = @MaxValue OUTPUT;

SELECT
    @FirstValue AS FirstValue,
    @LastValue AS LastValue,
    @CycleCount AS CycleCount,
    @Incr AS Increment,
    @MinValue AS MinValue,
    @MaxValue AS MaxValue;

Result:

FirstValue  LastValue  CycleCount  Increment  MinValue  MaxValue
----------  ---------  ----------  ---------  --------  --------
1           600        0           1          1         1000    

This example includes all arguments for sp_sequence_get_range, and we can see them returned in the results of our SELECT statement.

Here’s what happens when we run the procedure again:

FirstValue  LastValue  CycleCount  Increment  MinValue  MaxValue
----------  ---------  ----------  ---------  --------  --------
601         200        1           1          1         1000    

We can see that the values in the FirstValue and LastValue columns have changed. We can also see that the CycleCount column has changed to reflect that fact that we finished one cycle and started the next. This is because we used the CYCLE argument when we defined the sequence object.

Exceeding the Range Without CYCLE

If the sequence is defined as NO CYCLE (or simply without the CYCLE argument), then we would get an error when it exceeds the end of the sequence.