sp_sequence_get_range

This proc assigns range of sequence values  to current application along with metadata.

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 ]

    [ ; ]

 @sequence_name = Name of sequence and schema is optional. sequence_name is nvarchar(776).

 @range_size = The number of values to fetch from the sequence. @range_size is bigint.

 @range_first_value = Returns the first value of the sequence object . @range_first_value is sql_variant with.

 @range_last_value =Returns the last value of the requested range. @range_last_value is sql_variant .

 @range_cycle_count = Returns the number of times that the sequence object cycled . @range_cycle_count is int.

 @sequence_increment =Returns the increment of the sequence object used to calculate the requested range. @sequence_increment is sql_variant.

 @sequence_min_value = Returns the minimum value of the sequence object. @sequence_min_value is sql_variant with.

 @sequence_max_value = Returns the maximum value of the sequence object. @sequence_max_value is sql_variant.

 First we will create a sequence and use this proc to get result values

CREATE SEQUENCE TestSeq

     AS int

     START WITH 1

     INCREMENT BY 5

     MINVALUE 1

     MAXVALUE 500

     CYCLE

     CACHE 10;

DECLARE

   @range_last_value sql_variant ,

   @range_first_value sql_variant ,

   @range_cycle_count int ,

   @sequence_increment sql_variant ,

   @sequence_min_value sql_variant ,

   @sequence_max_value sql_variant;

EXEC Sp_sequence_get_range

  @sequence_name = N'TestSeq',

  @range_size = 10,

  @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_min_value OUTPUT;

SELECT @range_first_value  AS firstNumber,

       @range_last_value   AS lastnumber,

       @range_cycle_count  AS cycle_count,

       @sequence_increment AS increment_value,

       @sequence_min_value AS min_value,

       @sequence_max_value AS max_value

Here we can see that 1 is first number and 46 is last numbers.

so number upto 46 are assigned by this proc and it can be used current session

for whatever task.

Any query that will call this sequence will now get value above 46

Now we will change range_size to 1000

so it will have to cycle sequence multiple times

go

DECLARE

   @range_last_value sql_variant ,

   @range_first_value sql_variant ,

   @range_cycle_count int ,

   @sequence_increment sql_variant ,

   @sequence_min_value sql_variant ,

   @sequence_max_value sql_variant;

EXEC Sp_sequence_get_range

  @sequence_name = N'TestSeq',

  @range_size = 1000,

  @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_min_value OUTPUT;

SELECT @range_first_value  AS firstNumber,

       @range_last_value   AS lastnumber,

       @range_cycle_count  AS cycle_count,

       @sequence_increment AS increment_value,

       @sequence_min_value AS min_value,

       @sequence_max_value AS max_value

        

        

We can see that its cycled for 10 times.

Now what if cycle is not allowed

ALTER SEQUENCE testseq

     NO CYCLE;

DECLARE

   @range_last_value sql_variant ,

   @range_first_value sql_variant ,

   @range_cycle_count int ,

   @sequence_increment sql_variant ,

   @sequence_min_value sql_variant ,

   @sequence_max_value sql_variant;

EXEC Sp_sequence_get_range

  @sequence_name = N'TestSeq',

  @range_size = 1000,

  @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_min_value OUTPUT;

SELECT @range_first_value  AS firstNumber,

       @range_last_value   AS lastnumber,

       @range_cycle_count  AS cycle_count,

       @sequence_increment AS increment_value,

       @sequence_min_value AS min_value,

       @sequence_max_value AS max_value

We will get error here.

As cycle is not allowed and range_size 1000 requires it to cycle multiple times.