{"id":92987,"date":"2021-11-30T19:14:55","date_gmt":"2021-11-30T19:14:55","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=92987"},"modified":"2021-11-30T19:14:55","modified_gmt":"2021-11-30T19:14:55","slug":"how-to-return-multiple-sequence-numbers-with-sp_sequence_get_range","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/learn\/how-to-return-multiple-sequence-numbers-with-sp_sequence_get_range\/","title":{"rendered":"How to return multiple sequence numbers with sp_sequence_get_range"},"content":{"rendered":"

The series so far:<\/strong><\/p>\n

    \n
  1. Introduction to SQL Server sequence objects<\/a><\/li>\n
  2. Using SQL Server sequence objects<\/a><\/li>\n
  3. How to return multiple sequence numbers with sp_sequence_get_range<\/a><\/li>\n
  4. How to replace an identity column with a sequence number<\/a><\/li>\n<\/ol>\n\n

    Each time an application requests a sequence number using the NEXT VALUE FOR<\/code> 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<\/code> 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<\/code> 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.<\/p>\n

    Sp_sequence_get_range stored procedure<\/h2>\n

    The sp_sequence_get_range<\/code> 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\u2019t 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.<\/p>\n

    Below is syntax for the sp_sequence_get_range<\/code> stored procedure, as found in the Microsoft documentation:<\/p>\n

    sp_sequence_get_range [ @sequence_name = ] N'<sequence>'   \r\n     , [ @range_size = ] range_size  \r\n     , [ @range_first_value = ] range_first_value OUTPUT   \r\n    [, [ @range_last_value = ] range_last_value OUTPUT ]  \r\n    [, [ @range_cycle_count = ] range_cycle_count OUTPUT ]  \r\n    [, [ @sequence_increment = ] sequence_increment OUTPUT ]  \r\n    [, [ @sequence_min_value = ] sequence_min_value OUTPUT ]  \r\n    [, [ @sequence_max_value = ] sequence_max_value OUTPUT ]  \r\n    [ ; ]<\/pre>\n
    For a complete explanation of each of these parameters, please refer to the Microsoft documentation<\/a>.<\/p>\n
    To understand how this store procedure can be used to generate a range of sequence numbers, take a look at a few examples.<\/p>\n
    Sequence object for examples<\/h2>\n
    All the examples in this article will use a sequence object name CountTo7<\/code> that can be created by running the code in Listing 1.<\/p>\n
    Listing 1: Create CountTo7 sequence object<\/strong><\/p>\n
    USE tempdb;\r\nGO\r\nCREATE SEQUENCE CountTo7\r\n   AS int\r\n   START WITH 1\r\n   INCREMENT BY 1\r\n   MINVALUE 1\r\n   MAXVALUE 7\r\n   NO CYCLE; \r\nGO<\/pre>\n
    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<\/code> option has been specified.<\/p>\n
    Generating a range of three values<\/h2>\n
    For the first example, the sp_sequence_get_range <\/code>stored procedure will be called to return a range of three values from the CountTo7<\/code> 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.<\/p>\n
    The code in Listing 2 will call the sp_sequence_get_range<\/code> stored procedure requesting three values from the CountTo7<\/code> sequence objects. It will then use the output from the stored procedures to print out the next three sequence numbers.<\/p>\n
    Listing 2: Return a range of three values<\/strong><\/p>\n
    USE tempdb;\r\nGO\r\n-- declare variable \r\nDECLARE   \r\n  @RangeSize int = 3,   \r\n  @FirstSeqNum sql_variant,  \r\n  @LastSeqNum sql_variant,\r\n  @CycleCount sql_variant,\r\n  @SequenceIncrement sql_variant,\r\n  @MinValue sql_variant, \r\n  @MaxValue sql_variant, \r\n  @CurrentSeqNum int,\r\n  @I int = 0; \r\n--Get sequence object values to support range\r\nEXEC sys.sp_sequence_get_range  \r\n  @sequence_name = N'CountTo7', \r\n  @range_size = @RangeSize,\r\n  @range_first_value = @FirstSeqNum OUTPUT,\r\n  @range_last_value = @LastSeqNum OUTPUT,\r\n  @sequence_increment = @SequenceIncrement OUTPUT;\r\n-- Cycle Through Range\r\nSET @CurrentSeqNum = CAST(@FirstSeqNum AS int);\r\nWHILE @CurrentSeqNum <= CAST(@LastSeqNum as int)\r\nBEGIN\r\n    PRINT @CurrentSeqNum  \r\n    SET @CurrentSeqNum = @CurrentSeqNum + \r\n         CAST(@SequenceIncrement AS int);\r\nEND<\/pre>\n
    When the code in Listing 2 is run the output in Report 1 is produced.<\/p>\n
    Report 1: Output of PRINT statement in Listing 2<\/strong><\/p>\n
    \"An<\/p>\n
    The code in Listing 2 first declares some variables to capture the output of the sp_sequence_get_range <\/code>stored procedure. The code then calls the sp_sequence_get_range<\/code> stored procedure, which returns several output variables. The output variable @FirstSeqNum<\/code> contains the first variable in the range, the @LastSeqNum<\/code> contains the last sequence number in the range, and the @SequenceIncrement <\/em>variable contains the increment value for the @CountTo7<\/code> sequence objects. These variables are then used to process through a WHILE<\/code> loop until all values in the requested range are displayed using a PRINT<\/code> statement.<\/p>\n
    When the sp_sequence_get_range<\/code> stored procedure was called, it returned output values and updated the database metadata for the last value used by the CountTo7<\/code> sequence object. This can be verified by running the code in Listing 3.<\/p>\n
    Listing 3: What is the current sequence number value<\/strong><\/p>\n
    USE tempdb;\r\nGO\r\nSELECT name, current_value FROM sys.sequences\r\nWHERE name = 'CountTo7';<\/pre>\n
    Report 2 shows the current sequence number value for the CountTo7<\/code> sequence object.<\/p>\n
    Report 2: Current value of CountTo7<\/em> sequence object<\/strong><\/p>\n
    \"An<\/p>\n
    The last value stored in metadata will be used to determine the next sequence number to generate if the NEXT VALUE FOR<\/code> function or when the sp_sequence_get_range <\/em>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:<\/p>\n
    \"An<\/p>\n
    This message occurs because the CountTo7<\/code> 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.<\/p>\n
    Dealing with maximum values for non-cycling sequence object<\/h2>\n
    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 \u201cNo more values left\u201d will be displayed. If not, the code adjusts the range size setting based on the number of values left.<\/p>\n
    Listing 4: Code to handle maximum values<\/strong><\/p>\n
    USE tempdb;\r\nGO\r\n-- declare variable \r\nDECLARE   \r\n  @RangeSize int = 3,   \r\n  @FirstSeqNum sql_variant,  \r\n  @LastSeqNum sql_variant,\r\n  @CycleCount sql_variant,\r\n  @SequenceIncrement sql_variant,\r\n  @MinValue sql_variant, \r\n  @MaxValue sql_variant, \r\n  @CurrentSeqNum int,\r\n  @I int = 0,\r\n  @maximum_value int, \r\n  @increment int,\r\n  @current_value int,\r\n  @is_cycling bit; \r\n-- Get current values for sequence objecty\r\nSELECT @maximum_value = CAST(maximum_value as int), \r\n       @increment = CAST(increment as int),\r\n\t   @current_value = CAST(current_value as int),\r\n\t   @is_cycling = Cast(is_cycling AS bit)\r\nFROM sys.sequences WHERE name = 'CountTo7'\r\n-- Are any values left\r\nIF @current_value = @maximum_value \r\nBEGIN\r\n   PRINT 'No more values left'\r\n   RETURN\r\nEND\r\n-- Adjust range if not enough values left\r\nIF  (@current_value + (@increment * @RangeSize)) > @maximum_value\r\n\tAND @is_cycling = 0\r\n   SET @RangeSize = @maximum_value - @current_value;\r\n--Get range of sequence number\r\nEXEC sys.sp_sequence_get_range  \r\n  @sequence_name = N'CountTo7', \r\n  @range_size = @RangeSize,\r\n  @range_first_value = @FirstSeqNum OUTPUT,\r\n  @range_last_value = @LastSeqNum OUTPUT,\r\n  @sequence_increment = @SequenceIncrement OUTPUT;\r\n-- Cycle Through Range\r\nSET @CurrentSeqNum = CAST(@FirstSeqNum AS int);\r\nWHILE @CurrentSeqNum <= CAST(@LastSeqNum as int)\r\nBEGIN\r\n   PRINT @CurrentSeqNum  \r\n   SET @CurrentSeqNum = @CurrentSeqNum + \r\n          CAST(@SequenceIncrement AS int);\r\nEND<\/pre>\n
    I\u2019ll 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 \u201c7\u201d. For every execution after the first one, the message \u201cNo more values left\u201d will be displayed. It is worth noting that this code only works for sequence objects that have a positive increment value and don\u2019t cycle. If you need to handle sequence objects that cycle or count down, different code will be needed.<\/p>\n
    Handling cycling of sequence numbers<\/h2>\n
    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<\/code> can be used to determine if the range requested by the sp_sequence_get_range<\/code> 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.<\/p>\n
    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.<\/p>\n
    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.<\/p>\n
    Listing 5: Code to cycle through sequence numbers<\/strong><\/p>\n
    USE tempdb;\r\nGO\r\nDECLARE   \r\n-- Parameters for code\r\n  @RangeSize int = 3,   \r\n  @SequenceName varchar(100) = 'CountTo7', \r\n  -- Variables returned from sys.sp_sequence_get_range  \r\n  @RangeFirstValue sql_variant,  \r\n  @RangeLastValue sql_variant,\r\n  @RangeCycleCount int,\r\n  -- values returned from sys.sequences \r\n  @SequenceMinValue int, \r\n  @SequenceMaxValue int,\r\n  @SequenceIncrement int,\r\n  @SequenceCurrentValue int,\r\n  @SequenceIsCycling bit,\r\n  -- temp variables\r\n  @I int = 0,\r\n  @CurrentSeqNum INT;\r\n  \r\n-- Get current values for sequence settings\r\nSELECT @SequenceMinValue = CAST(minimum_value as int), \r\n       @SequenceMaxValue = CAST(maximum_value as int), \r\n       @SequenceIncrement= CAST(increment as int),\r\n\t   @SequenceCurrentValue = CAST(current_value as int),\r\n\t   @SequenceIsCycling = Cast(is_cycling AS bit)\r\nFROM sys.sequences where name = @SequenceName;\r\n-- Adjust range to reflect number of values left if not cycling\r\nIF @SequenceIsCycling = 0  AND (@SequenceMaxValue - \r\n           @SequenceCurrentValue) \/ @SequenceIncrement < @RangeSize\r\n   AND @SequenceIncrement > 0\r\n   SET @RangeSize = (@SequenceMaxValue - \r\n         @SequenceCurrentValue) \/ @SequenceIncrement\r\nIF @SequenceIsCycling = 0  AND \r\n        (@SequenceMinValue - @SequenceCurrentValue) \/ \r\n           @SequenceIncrement < @RangeSize\r\n   AND @SequenceIncrement < 0\r\n   SET @RangeSize = (@SequenceMinValue - \r\n         @SequenceCurrentValue) \/ @SequenceIncrement\r\n-- Are the more sequences available\r\nIF @RangeSize > 0\r\n   --Get range of values\r\n   EXEC sys.sp_sequence_get_range  \r\n     @sequence_name = @SequenceName, \r\n     @range_size = @RangeSize,\r\n     @range_first_value = @RangeFirstValue OUTPUT,\r\n     @range_last_value = @RangeLastValue OUTPUT, \r\n     @range_cycle_count = @RangeCycleCount OUTPUT;\r\nELSE\r\n-- No more \r\n  PRINT 'No more sequence numbers to return';\r\nSET @CurrentSeqNum = CAST(@RangeFirstValue AS INT);\r\n-- Cycle Through Range\r\nWHILE @RangeSize > 0  \r\nBEGIN\r\n    IF @CurrentSeqNum > @SequenceMaxValue\r\n\t   SET @CurrentSeqNum =  @SequenceMinValue;\r\n\tIF @CurrentSeqNum < @SequenceMinValue\r\n\t   SET @CurrentSeqNum = @SequenceMaxValue\r\n\tPRINT @CurrentSeqNum;\r\n\tSET @CurrentSeqNum = @CurrentSeqNum + @SequenceIncrement;\r\n\tSET @RangeSize = @RangeSize - 1\r\nEND<\/pre>\n
    To verify the code in Listing 5 will cycle through the values of the CountTo7<\/code> sequence object, the object first needs to be altered, so it supports cycling by using the code in Listing 6.<\/p>\n
    Listing 6: Altering sequence object to support cycling<\/strong><\/p>\n
    USE tempdb;\r\nGO\r\nALTER Sequence CountTo7 \r\n   RESTART WITH 7\r\n   CYCLE;\r\nGO<\/pre>\n
    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<\/code> to NULL. This can be verified by running the code in Listing 7.<\/p>\n
    Listing 7: Reviewing metadata for CountTo7<\/em> sequence object <\/strong><\/p>\n
    USE tempdb;\r\nGO\r\nSELECT name, current_value, last_used_value FROM sys.sequences\r\nWHERE name = 'CountTo7';<\/pre>\n
    When the code in Listing 7 is run the output in Report 3 is displayed.<\/p>\n
    Report 3: Output when Listing 7 is run <\/strong><\/p>\n
    \"An<\/p>\n
    With the CountTo7<\/code> 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.<\/p>\n
    Report 4: Output from the first execution of Listing 5<\/strong><\/p>\n
    \"Image<\/p>\n
    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.<\/p>\n
    The @range_cycle_count<\/code> 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 <\/em>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<\/code> sequence object.<\/p>\n
    To test if the code in Listing 5 will cycle through the CountTo7 <\/em>sequence values more than once, all that needs to be done is to pick a @RangeSize<\/code> parameter setting greater than 7. I\u2019ll leave it up to you to test out different @RangeSize<\/code> values to verify that this code can cycle multiple times through a range of sequence numbers.<\/p>\n
    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.<\/p>\n
    Missing sequence number values<\/h2>\n
    The sp_seqeunce_get_range<\/code> 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.<\/p>\n
    Return multiple sequence numbers with sp_sequence_get_range<\/h1>\n
    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<\/code> 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<\/code> system stored procedure to guarantee your range of sequence numbers does not have missing values.<\/p>\n
    If you liked this article, you might also like\u00a0<\/p>\n","protected":false},"excerpt":{"rendered":"
    Developers can work with multiple range values at once using sp_sequence_get_range. Greg Larsen explains how to return multiple sequence numbers with sp_sequence_get_range.…<\/p>\n","protected":false},"author":78478,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[53,143525],"tags":[5134],"coauthors":[11330],"class_list":["post-92987","post","type-post","status-publish","format-standard","hentry","category-featured","category-learn","tag-sql-prompt"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/92987","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/78478"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=92987"}],"version-history":[{"count":8,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/92987\/revisions"}],"predecessor-version":[{"id":93004,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/92987\/revisions\/93004"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=92987"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=92987"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=92987"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=92987"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}