If you’re getting an error that reads something like “The requested range for sequence object ‘Sequence1’ exceeds the maximum or minimum limit. Retry with a smaller range.” in SQL Server, it’s probably because you’re trying to return a value that’s outside the range of the sequence object.
This can happen when you’re using the sp_sequence_get_range stored procedure to return a range from a sequence object that exceeds the minimum or maximum value for that sequence object, and the sequence object does not have CYCLE defined. It’s a similar error to error 11728, which can happen when using NEXT VALUE FOR to generate a new value from a sequence.
To fix this issue, either use a range that fits within the range of the sequence object, or change the sequence object to CYCLE.
Example of Error
Suppose we create the following sequence object:
CREATE SEQUENCE Sequence1
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 1000
NO CYCLE;This sequence is set to NO CYCLE, which means that it won’t cycle back to the MINVALUE after it reaches MAXVALUE. Instead, it will return the error mentioned above.
So let’s pass our sequence to the sp_sequence_get_range stored procedure:
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
So far, so good. This is what we’d normally expect from the sp_sequence_get_range stored procedure. We specified a @range_size of 600, which is reflected in the results with the difference between the FirstValue and LastValue columns.
However, if we run the same code again, we end up with the following error:
Msg 11732, Level 16, State 1, Procedure sys.sp_sequence_get_range_internal, Line 1 The requested range for sequence object 'Sequence1' exceeds the maximum or minimum limit. Retry with a smaller range.
We get this error because we exceeded the sequence’s MAXVALUE of 1000. This is because our @range_size of 600 would have resulted in a LastValue of 1200, which is outside the range of our sequence object (which has a range of 1000).
Solution 1
One way to fix this issue is to change our @range_size to a smaller 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 = 200,
@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 ---------- --------- ---------- --------- -------- -------- 601 800 0 1 1 1000
Solution 2
Another way to fix it is to change the range of the sequence object:
ALTER SEQUENCE Sequence1 MAXVALUE 2000;Now we can run the code with the original @range_size again without error:
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 ---------- --------- ---------- --------- -------- -------- 801 1400 0 1 1 2000
Solution 3
Another way to do eliminate the error is to change the sequence object to a cycling sequence. While this likely won’t be appropriate in many cases, it’s still an option:
ALTER SEQUENCE Sequence1 CYCLE;Now we can exceed the MAXVALUE without error, because it will cycle back through the range each time the end of the range has been reached
Let’s increase the @range_size and run the stored procedure again:
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 = 800,
@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 ---------- --------- ---------- --------- -------- -------- 1401 200 1 1 1 2000
This time it cycled back to the start of the sequence’s range and continued until the 800 range size was met.