SQL Server Error Messages - Msg 11702

Error Message

Server: Msg 11702, Level 16, State 2, Line 1
The sequence object '<Sequence Name>' must be of data type int, bigint, smallint, tinyint, or decimal or numeric 
with a scale of 0, or any user-defined data type that is based on one of the above integer data types.

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 } ]

A sequence can be defined as any integer type. The following types are allowed: TINYINT, SMALLINT, INT, BIGINT and DECIMAL and NUMERIC with a scale of 0. Any other data types not listed here will generate this error message.

Here are three examples of creating a sequence that specifies a non-integer data type as its output and thus raising this error message:

CREATE SEQUENCE [dbo].[BinarySequence] AS BIT;

Msg 11702, Level 16, State 2, Line 1
The sequence object 'dbo.BinarySequence' must be of data type int, bigint, smallint, tinyint, or decimal or numeric 
with a scale of 0, or any user-defined data type that is based on one of the above integer data types.

CREATE SEQUENCE [dbo].[DateSequence] AS DATE;

Msg 11702, Level 16, State 2, Line 1
The sequence object 'dbo.DateSequence' must be of data type int, bigint, smallint, tinyint, or decimal or numeric 
with a scale of 0, or any user-defined data type that is based on one of the above integer data types.

CREATE SEQUENCE [dbo].[AlphabetSequence] AS CHAR(1);

Msg 11702, Level 16, State 2, Line 1
The sequence object 'dbo.AlphabetSequence' must be of data type int, bigint, smallint, tinyint, or decimal or numeric 
with a scale of 0, or any user-defined data type that is based on one of the above integer data types.

Solution / Work Around

As mentioned earlier, a sequence only generates a sequence of numeric values, integer values in particular. Depending on the requirements, a sequence can still be used to generate other data types but the sequence may or may not be used directly. Based on the 3 examples shown earlier where an output of a BIT, DATE and CHAR data types are desired, the following workarounds can be used.

In the first case, a BIT data type is required. The following sequence definition can be used to emulate this scenario.

-- Case #1: BIT Data Type
CREATE SEQUENCE [dbo].[BinarySequence] AS TINYINT
START WITH 0 INCREMENT BY 1 MAXVALUE 1 CYCLE;
GO

SELECT NEXT VALUE FOR [dbo].[BinarySequence]
GO

Although the data type of the output of this sequence is TINYINT, the values returned by the sequence will only be 0 and 1.

In the second case, a DATE data type is required. Since a sequence cannot be defined to return a DATE data type, an integer data type will be returned instead and the output of the sequence will then be added to a base date to simulate a DATE sequence.

-- Case #2: Date Data Type
CREATE SEQUENCE [dbo].[DateSequence] AS INT
START WITH 1 INCREMENT BY 1;
GO

SELECT DATEADD(DD, NEXT VALUE FOR [dbo].[DateSequence], CAST('2014/01/01' AS DATE ))
GO

In the third case, a CHAR data type is required. To be more specific, the desired output is the alphabet. A workaround in generating the alphabet using a sequence is with the use of the CHAR string function. The CHAR string function converts an INT ASCII code to a character. The ASCII value for the uppercase alphabet starts with 65 for the letter A until 90 for the letter Z. Given this, a sequence can be created that will return numbers 65 through 90 and the output of this sequence can be passed to the CHAR string function which will convert it to the corresponding letter of the alphabet, as can be seen in the following script.

-- Case #3: CHAR Data Type
CREATE SEQUENCE [dbo].[AlphabetSequence] AS INT
START WITH 65 INCREMENT BY 1
MINVALUE 65 MAXVALUE 90 CYCLE
GO

SELECT CHAR(NEXT VALUE FOR [dbo].[AlphabetSequence])
GO