If you’re getting an error that reads something like “The sequence object ‘…’ has reached its minimum or maximum value. Restart the sequence object to allow new values to be generated.“, it’s probably because you’re using a sequence that’s reached the minimum or maximum value specified in its definition.

For example, if the sequence was defined with MAXVALUE 100, and it has a positive increment of INCREMENT BY 1, then once the count reaches 100, it won’t be able to increment any more. Therefore, any call to that sequence will result in the above error.

There are a few options for fixing this issue. One option is to increase the maximum value of the sequence. Another option is to set the sequence to cycle (so that it reuses previously used numbers). Another option is to restart the sequence. The option we choose depends on our requirements.

Example of Error

Suppose we create a sequence using the following code:

CREATE SEQUENCE Sequence1
    START WITH 1
    INCREMENT BY 1
    MINVALUE 1
    MAXVALUE 3;

Now let’s use that sequence to number the result set of a SELECT query:

SELECT 
    NEXT VALUE FOR Sequence1 AS CustomerGroup,
    CustomerName
FROM Customers;

Result:

Msg 11728, Level 16, State 1, Line 1
The sequence object 'Sequence1' has reached its minimum or maximum value. Restart the sequence object to allow new values to be generated.

Here, I got the error because there were five rows in the result set, but the sequence doesn’t go up as high as that. The sequence only goes up to three.

Our solution to the issue depends on our requirements.

Solution 1

One option is to modify the sequence so that its maximum value is high enough to cater for our needs:

ALTER SEQUENCE Sequence1 MAXVALUE 12;

This will allow us to run the query again without getting the error:

SELECT 
    NEXT VALUE FOR Sequence1 AS CustomerGroup,
    CustomerName
FROM Customers;

Result:

CustomerGroup  CustomerName      
-------------  ------------------
4              Palm Pantry       
5              Tall Poppy        
6              Crazy Critters    
7              Oops Media        
8              Strange Names Inc.

This time we were able to run the query without error.

However, if we run the query again, we’ll get the error again:

SELECT 
    NEXT VALUE FOR Sequence1 AS CustomerGroup,
    CustomerName
FROM Customers;

Result:

Error: The sequence object 'Sequence1' has reached its minimum or maximum value. Restart the sequence object to allow new values to be generated.

If this is still an issue, we can use an even larger value for the MAXVALUE, or we can try one of the following solutions.

Solution 2

Another thing we can do is restart the sequence. This resets the count back to a value that we specify, or to the value specified in the original CREATE SEQUENCE statement:

ALTER SEQUENCE Sequence1 RESTART WITH 1;

Now, when we call NEXT VALUE again, it will be 1:

SELECT NEXT VALUE FOR Sequence1;

Result:

1

However, once the count gets back to the maximum value, we’ll get the same error again.

Solution 3

Another option is to cycle the sequence. This enables the sequence to reuse previous values once the maximum value has been reached. We can do this with the CYCLE argument:

ALTER SEQUENCE Sequence1 CYCLE;

Now, when we run the query again, the numbering will reset to 1 once it has passed its maximum value:

SELECT 
    NEXT VALUE FOR Sequence1 AS CustomerGroup,
    CustomerName
FROM Customers;

Result:

CustomerGroup  CustomerName      
-------------  ------------------
1              Palm Pantry       
2              Tall Poppy        
3              Crazy Critters    
1              Oops Media        
2              Strange Names Inc.

When using the CYCLE option, the count restarts at the MINVALUE value or the MAXVALUE if incrementing by a negative amount.