|
|
Error Message
Server: Msg 11708, Level 16, State 1, Line 1
An invalid value was specified for argument '<Argument
Name>' for the given data type.
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 START WITH argument specifies the first value returned by the sequence object. The START value specified must be greater than or equal to the minimum value of the sequence object (whether explicitly specified in the MINVALUE argument or implicitly set by the data type of the object sequence) and less than or equal to the maximum value of the sequence object (whether explicitly specified in the MAXVALUE argument or implicitly set by the data type of the object sequence).
The INCREMENT BY argument specifies the increment (or decrement if negative) the value of the sequence object for each call to the NEXT VALUE FOR function. If the value specified in this INCREMENT BY argument is a negative value, the sequence object is descending; otherwise, it is ascending. The increment cannot be 0 and if not specified, the default value for a new sequence object is 1.
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.
If the value specified in any of these arguments, namely START WITH, INCREMENT BY, MINVALUE and MAXVALUE, are not within the allowed values by the data type of the sequence object, this error message will be raised. Here are a few examples of how this error message can be encountered:
CREATE SEQUENCE [dbo].[CountdownSequence] AS TINYINT
START WITH 500
GO
Msg 11708, Level 16, State 1, Line 1
An invalid value was specified for argument 'START WITH' for the given data type.
CREATE SEQUENCE [dbo].[NegativeSequence] AS TINYINT
INCREMENT BY -1
GO
Msg 11708, Level 16, State 1, Line 1
An invalid value was specified for argument 'INCREMENT BY' for the given data type.
CREATE SEQUENCE [dbo].[NegativeToPositiveSequence] AS TINYINT
MINVALUE -100 MAXVALUE 100
Msg 11708, Level 16, State 1, Line 1
An invalid value was specified for argument 'MINVALUE' for the given data type.
CREATE SEQUENCE [dbo].[Top500Sequence] AS TINYINT
START WITH 1 INCREMENT BY 1 MAXVALUE 500
Msg 11708, Level 16, State 1, Line 1
An invalid value was specified for argument 'MAXVALUE' for the given data type.
Solution / Work Around:
To avoid encountering this error message, make sure that the values specified in the START WITH, INCREMENT BY, MINVALUE and MAXVALUE arguments of the sequence object creation are within the allowed values for the data type of sequence object. As a reference, here are the range values for each integer data type:
- TINYINT – Range 0 to 255
- SMALLINT – Range -32,768 to 32,767
- INT – Range -2,147,483,648 to 2,147,483,647
- BIGINT – Range -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
Since the data type of the sequence objects being created above are of TINYINT, the valid range values are only from 0 to 255, thus generating this error message. Here is the updated sequence creation script that fixes the error encountered above:
CREATE SEQUENCE [dbo].[CountdownSequence] AS SMALLINT -- OR INT
START WITH 500
CREATE SEQUENCE [dbo].[NegativeSequence] AS SMALLINT -- OR INT
INCREMENT BY -1
CREATE SEQUENCE [dbo].[NegativeToPositiveSequence] AS SMALLINT -- OR INT
MINVALUE -100 MAXVALUE 100
CREATE SEQUENCE [dbo].[Top500Sequence] AS SMALLINT -- OR INT
START WITH 1 INCREMENT BY 1 MAXVALUE 500
|