|
|
Error Message
Server: Msg 11705, Level 16, State 2, Line 1
The minimum value for sequence object '<Sequence Name>'
must be less than its maximum value.
Causes
A sequence is a user-defined schema-bound object that generates a sequence of numeric values according to the specification with which the sequence object was created.
|
The sequence of numeric values is generated in an ascending or descending order at a defined interval and may cycle or repeat as requested. Sequences, unlike identity columns, are not associated with tables.
A sequence is created independently of table objects by using the CREATE SEQUENCE statement. Options enable you to control the increment, maximum and minimum values, starting point, automatic restarting capability, and caching to improve performance.
The syntax of the CREATE SEQUENCE statement is as follows:
CREATE SEQUENCE <Sequence Name> AS <integer_type>
[ START WITH <constant> ]
[ INCREMENT BY <constant> ]
[ { MINVALUE [ <constant> ] } | { NO MINVALUE } ]
[ { MAXVALUE [ <constant> ] } | { NO MAXVALUE } ]
[ CYCLE | { NO CYCLE } ]
[ { CACHE [ <constant> ] } | { NO CACHE } ]
The MINVALUE and MAXVALUE specify the bounds for the sequence object. If the MINVALUE argument is not specified, the default minimum value for a new sequence object will be the minimum value of the data type of the sequence object. This is zero for the TINYINT data type and a negative number for all other data types. Similarly, if the MAXVALUE argument is not specified, the default maximum value for a new sequence object will be the maximum value of the data type of the sequence object.
The value specified in the MINVALUE must be less than the value specified in the MAXVALUE, otherwise this error message will be raised. Here are a couple of examples of sequence object creation that will generate this error message:
CREATE SEQUENCE [dbo].[CountdownSequence] AS INT
START WITH 100 INCREMENT BY -1 MINVALUE 100 MAXVALUE 0 CYCLE
Msg 11705, Level 16, State 1, Line 1
The minimum value for sequence object 'dbo.CountdownSequence' must be less than its maximum value.
CREATE SEQUENCE [dbo].[NegativeSequence] AS INT
START WITH -1 INCREMENT BY -1 MINVALUE -1 MAXVALUE -100 CYCLE
Msg 11705, Level 16, State 1, Line 1
The minimum value for sequence object 'dbo.NegativeSequence' must be less than its maximum value.
Solution / Work Around:
As the error message suggests, the value specified as the minimum value of a sequence object must be less than the maximum value specified. Even if the increment specified in the INCREMENT BY argument is a negative value, the MINVALUE must still be less than the MAXVALUE. The MINVALUE is not the starting value and the MAXVALUE is not the ending value of the sequence object.
Here is the updated sequence creation script that fixes the error encountered earlier:
CREATE SEQUENCE [dbo].[CountdownSequence] AS INT
START WITH 100 INCREMENT BY -1 MINVALUE 0 MAXVALUE 100 CYCLE
CREATE SEQUENCE [dbo].[NegativeSequence] AS INT
START WITH -1 INCREMENT BY -1 MINVALUE -100 MAXVALUE -1 CYCLE
|