How to return multiple sequence numbers with sp_sequence_get_range

The series so far:

1. Introduction to SQL Server sequence objects
2. Using SQL Server sequence objects
3. How to return multiple sequence numbers with sp_sequence_get_range

Each time an application requests a sequence number using the NEXT VALUE FOR function, they get a new sequence number. The last used sequence number gets updated in the database metadata. If an application requires a sequence number series to be sequential, the NEXT VALUE FOR function cannot guarantee that all sequence numbers returned will be sequential. This behavior is caused because multiple sessions could be requesting sequence numbers at relatively the same time. If an application requires multiple sequence numbers, and all the sequence numbers need to be sequential, then the sp_sequence_get_range stored procedure should be used. This article will explore how an application can use this stored procedure to generate a range of sequential sequence numbers.

Sp_sequence_get_range stored procedure

The sp_sequence_get_range stored procedure is a system stored procedure that comes with SQL Server. It supports returning a range of sequence numbers for a sequence object. In reality, this stored procedure doesn’t really return a range of values but instead returns a series of output parameter values. The output parameters can then be used to generate a range of sequence number values programmatically. The stored procedure will also support cycling sequence numbers when the minimum or maximum values of the sequence object are reached. When this stored procedure is called, in addition to returning the output values, it also updates the last sequence number used in the Database metadata as if the entire range of sequence numbers was returned.

Below is syntax for the sp_sequence_get_range stored procedure, as found in the Microsoft documentation:

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 ]  
    [ ; ]

For a complete explanation of each of these parameters, please refer to the Microsoft documentation.

To understand how this store procedure can be used to generate a range of sequence numbers, take a look at a few examples.

Sequence object for examples

All the examples in this article will use a sequence object name CountTo7 that can be created by running the code in Listing 1.

Listing 1: Create CountTo7 sequence object

USE tempdb;
GO
CREATE SEQUENCE CountTo7
   AS int
   START WITH 1
   INCREMENT BY 1
   MINVALUE 1
   MAXVALUE 7
   NO CYCLE; 
GO

This sequence object is defined as an integer. It can be used to generate the following series of sequence numbers: 1, 2, 3, 4, 5, 6, and 7. When a sequence number value of 7 is reached, the sequence number will not cycle because the NO CYCLE option has been specified.

Generating a range of three values

For the first example, the sp_sequence_get_range stored procedure will be called to return a range of three values from the CountTo7 sequence object. This procedure will not return the range of sequence numbers. Instead, it returns only output variables that can be used to generate the range of three values programmatically. Additionally, when this stored procedure is called, it will update the last sequence number as if all three different sequence numbers have been generated.

The code in Listing 2 will call the sp_sequence_get_range stored procedure requesting three values from the CountTo7 sequence objects. It will then use the output from the stored procedures to print out the next three sequence numbers.

Listing 2: Return a range of three values

USE tempdb;
GO
-- declare variable 
DECLARE   
  @RangeSize int = 3,   
  @FirstSeqNum sql_variant,  
  @LastSeqNum sql_variant,
  @CycleCount sql_variant,
  @SequenceIncrement sql_variant,
  @MinValue sql_variant, 
  @MaxValue sql_variant, 
  @CurrentSeqNum int,
  @I int = 0; 
--Get sequence object values to support range
EXEC sys.sp_sequence_get_range  
  @sequence_name = N'CountTo7', 
  @range_size = @RangeSize,
  @range_first_value = @FirstSeqNum OUTPUT,
  @range_last_value = @LastSeqNum OUTPUT,
  @sequence_increment = @SequenceIncrement OUTPUT;
-- Cycle Through Range
SET @CurrentSeqNum = CAST(@FirstSeqNum AS int);
WHILE @CurrentSeqNum <= CAST(@LastSeqNum as int)
BEGIN
    PRINT @CurrentSeqNum  
    SET @CurrentSeqNum = @CurrentSeqNum + 
         CAST(@SequenceIncrement AS int);
END

When the code in Listing 2 is run the output in Report 1 is produced.

Report 1: Output of PRINT statement in Listing 2

The code in Listing 2 first declares some variables to capture the output of the sp_sequence_get_range stored procedure. The code then calls the sp_sequence_get_range stored procedure, which returns several output variables. The output variable @FirstSeqNum contains the first variable in the range, the @LastSeqNum contains the last sequence number in the range, and the @SequenceIncrement variable contains the increment value for the @CountTo7 sequence objects. These variables are then used to process through a WHILE loop until all values in the requested range are displayed using a PRINT statement.

When the sp_sequence_get_range stored procedure was called, it returned output values and updated the database metadata for the last value used by the CountTo7 sequence object. This can be verified by running the code in Listing 3.

Listing 3: What is the current sequence number value

USE tempdb;
GO
SELECT name, current_value FROM sys.sequences
WHERE name = 'CountTo7';

Report 2 shows the current sequence number value for the CountTo7 sequence object.

Report 2: Current value of CountTo7 sequence object

The last value stored in metadata will be used to determine the next sequence number to generate if the NEXT VALUE FOR function or when the sp_sequence_get_range stored procedure is called. If Listing 2 is run a second time, it will print sequence values 4, 5, and 6. However, if it is run a third time, the following error will occur:

This message occurs because the CountTo7 sequence object is set up to not cycle, and there is only one more sequence number available before reaching the limit. To avoid this error, the code needs to be modified to reach the maximum for this non-cycling sequence object.

Dealing with maximum values for non-cycling sequence object

Some additional code will need to be written to programmatically retrieve the last range of sequence numbers for a non-cycling sequence object. In Listing 4, the code determines whether or not the maximum sequence value has been reached. If it has been reached, the message “No more values left” will be displayed. If not, the code adjusts the range size setting based on the number of values left.

Listing 4: Code to handle maximum values

USE tempdb;
GO
-- declare variable 
DECLARE   
  @RangeSize int = 3,   
  @FirstSeqNum sql_variant,  
  @LastSeqNum sql_variant,
  @CycleCount sql_variant,
  @SequenceIncrement sql_variant,
  @MinValue sql_variant, 
  @MaxValue sql_variant, 
  @CurrentSeqNum int,
  @I int = 0,
  @maximum_value int, 
  @increment int,
  @current_value int,
  @is_cycling bit; 
-- Get current values for sequence objecty
SELECT @maximum_value = CAST(maximum_value as int), 
       @increment = CAST(increment as int),
           @current_value = CAST(current_value as int),
           @is_cycling = Cast(is_cycling AS bit)
FROM sys.sequences WHERE name = 'CountTo7'
-- Are any values left
IF @current_value = @maximum_value 
BEGIN
   PRINT 'No more values left'
   RETURN
END
-- Adjust range if not enough values left
IF  (@current_value + (@increment * @RangeSize)) > @maximum_value
        AND @is_cycling = 0
   SET @RangeSize = @maximum_value - @current_value;
--Get range of sequence number
EXEC sys.sp_sequence_get_range  
  @sequence_name = N'CountTo7', 
  @range_size = @RangeSize,
  @range_first_value = @FirstSeqNum OUTPUT,
  @range_last_value = @LastSeqNum OUTPUT,
  @sequence_increment = @SequenceIncrement OUTPUT;
-- Cycle Through Range
SET @CurrentSeqNum = CAST(@FirstSeqNum AS int);
WHILE @CurrentSeqNum <= CAST(@LastSeqNum as int)
BEGIN
   PRINT @CurrentSeqNum  
   SET @CurrentSeqNum = @CurrentSeqNum + 
          CAST(@SequenceIncrement AS int);
END

I’ll leave it up to you to run the code in Listing 4. The first time you run it, it should adjust the range and return the last value left for this sequence object, which would be “7”. For every execution after the first one, the message “No more values left” will be displayed. It is worth noting that this code only works for sequence objects that have a positive increment value and don’t cycle. If you need to handle sequence objects that cycle or count down, different code will be needed.

Handling cycling of sequence numbers

When a sequence object supports cycling, the code to return ranges of sequence numbers gets a little more complicated. The output parameter @range_cycle_count can be used to determine if the range requested by the sp_sequence_get_range stored procedure has cycled. This output parameter indicates the number of times a range has cycled, where 0 indicates the range has not cycled through all the available values of a sequence object. A positive number tells the number of times a sequence object has cycled, based on its minimum or maximum value.

When a range of numbers is cycled, the first sequence number used after cycling depends on the increment value. If the increment value is positive, the minimum value is used as the first sequence number after cycling. If the increment value is negative, the maximum value is used as the first sequence number after cycling.

Run the code in Listing 5 to cycle through a range of numbers. This code was built by refactoring the code in Listing 4 and adding additional code to cycle up or down through the sequence numbers depending on whether the increment value is negative or positive.

Listing 5: Code to cycle through sequence numbers

USE tempdb;
GO
DECLARE   
-- Parameters for code
  @RangeSize int = 3,   
  @SequenceName varchar(100) = 'CountTo7', 
  -- Variables returned from sys.sp_sequence_get_range  
  @RangeFirstValue sql_variant,  
  @RangeLastValue sql_variant,
  @RangeCycleCount int,
  -- values returned from sys.sequences 
  @SequenceMinValue int, 
  @SequenceMaxValue int,
  @SequenceIncrement int,
  @SequenceCurrentValue int,
  @SequenceIsCycling bit,
  -- temp variables
  @I int = 0,
  @CurrentSeqNum INT;
  
-- Get current values for sequence settings
SELECT @SequenceMinValue = CAST(minimum_value as int), 
       @SequenceMaxValue = CAST(maximum_value as int), 
       @SequenceIncrement= CAST(increment as int),
           @SequenceCurrentValue = CAST(current_value as int),
           @SequenceIsCycling = Cast(is_cycling AS bit)
FROM sys.sequences where name = @SequenceName;
-- Adjust range to reflect number of values left if not cycling
IF @SequenceIsCycling = 0  AND (@SequenceMaxValue - 
           @SequenceCurrentValue) / @SequenceIncrement < @RangeSize
   AND @SequenceIncrement > 0
   SET @RangeSize = (@SequenceMaxValue - 
         @SequenceCurrentValue) / @SequenceIncrement
IF @SequenceIsCycling = 0  AND 
        (@SequenceMinValue - @SequenceCurrentValue) / 
           @SequenceIncrement < @RangeSize
   AND @SequenceIncrement < 0
   SET @RangeSize = (@SequenceMinValue - 
         @SequenceCurrentValue) / @SequenceIncrement
-- Are the more sequences available
IF @RangeSize > 0
   --Get range of values
   EXEC sys.sp_sequence_get_range  
     @sequence_name = @SequenceName, 
     @range_size = @RangeSize,
     @range_first_value = @RangeFirstValue OUTPUT,
     @range_last_value = @RangeLastValue OUTPUT, 
     @range_cycle_count = @RangeCycleCount OUTPUT;
ELSE
-- No more 
  PRINT 'No more sequence numbers to return';
SET @CurrentSeqNum = CAST(@RangeFirstValue AS INT);
-- Cycle Through Range
WHILE @RangeSize > 0  
BEGIN
    IF @CurrentSeqNum > @SequenceMaxValue
           SET @CurrentSeqNum =  @SequenceMinValue;
        IF @CurrentSeqNum < @SequenceMinValue
           SET @CurrentSeqNum = @SequenceMaxValue
        PRINT @CurrentSeqNum;
        SET @CurrentSeqNum = @CurrentSeqNum + @SequenceIncrement;
        SET @RangeSize = @RangeSize - 1
END

To verify the code in Listing 5 will cycle through the values of the CountTo7 sequence object, the object first needs to be altered, so it supports cycling by using the code in Listing 6.

Listing 6: Altering sequence object to support cycling

USE tempdb;
GO
ALTER Sequence CountTo7 
   RESTART WITH 7
   CYCLE;
GO

Listing 6, in addition to altering the object to cycle, also sets the sequence object to restart at 7. Setting this sequence object to restart at 7 updated the current_value in metadata to 7 and reset the last_used_value to NULL. This can be verified by running the code in Listing 7.

Listing 7: Reviewing metadata for CountTo7 sequence object

USE tempdb;
GO
SELECT name, current_value, last_used_value FROM sys.sequences
WHERE name = 'CountTo7';

When the code in Listing 7 is run the output in Report 3 is displayed.

Report 3: Output when Listing 7 is run

With the CountTo7 sequence object set up to cycle, the code in Listing 5 can be executed. The first time this code is run, it will generate the range shown in Report 4.

Report 4: Output from the first execution of Listing 5

The sequence started generating values starting at 7 and then cycled, creating values 1 and 2, as the last two values in the range. The second time the code in Listing 5 is executed, it will return 3, 4, and 5. Each time it is rerun, it will generate the next three sequence number values based on the information stored in the database metadata.

The @range_cycle_count variable in the Listing 5 code is used to determine if the range of numbers cycled past the maximum value. In Listing 5, the sp_sequence_get_range stored procedure will never cycle through the range of values more than one time. This behavior happens because a range of 3 values is too small to cycle through the complete list of 7 values associated with the @CountTo7 sequence object.

To test if the code in Listing 5 will cycle through the CountTo7 sequence values more than once, all that needs to be done is to pick a @RangeSize parameter setting greater than 7. I’ll leave it up to you to test out different @RangeSize values to verify that this code can cycle multiple times through a range of sequence numbers.

The code in Listing 5 not only supports cycling but also has been written to handle sequence objects with a negative increment value. Keep in mind the code shown here is only sample code and has not been thoroughly tested to cover all situations. Therefore, use it at your own risk and fully test it before using this sample code in your application.

Missing sequence number values

The sp_seqeunce_get_range has a few issues of which you should be aware. When this stored procedure is called to retrieve a range, it updates the last used sequence number for the sequence object in the database metadata. If all of the values in the range requested are not used, any unused values will be lost. Additionally, if the database engine crashes, the last used sequence number stored in metadata will not be rolled back. Therefore, any values not used before the crash will also be lost.

Return multiple sequence numbers with sp_sequence_get_range

The sequence object was introduced with SQL Server 2012. Sequence objects can be used to populate multiple columns in a single table, as well as to synchronize a series of generated numbers across multiple tables. Using the sequence object to generate a series of numbers has more flexibility than using an identity column. When multiple sessions request sequence numbers simultaneously, the numbers generated for a given session may not be continuous sequence numbers. When this is an issue, the sp_sequence_get_range stored procedure can be used. By using this stored procedure, application code can be written, so a range of sequential sequence numbers can be obtained for a session. The next time you need to ensure that sequence number values are contiguous, you should use the sp_sequence_get_range system stored procedure to guarantee your range of sequence numbers does not have missing values.

If you liked this article, you might also like 

The post How to return multiple sequence numbers with sp_sequence_get_range appeared first on Simple Talk.

from Simple Talk https://ift.tt/3rouVuf
via