SQL Server Error Messages - Msg 11711

Error Message

Server: Msg 11711, Level 15, State 1, Line 1
Argument 'AS' cannot be used in an ALTER SEQUENCE statement.

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

To change the definition of an existing sequence object, the ALTER SEQUENCE statement is used. Similar to the CREATE SEQUENCE, the ALTER SEQUENCE has options that enable you to control the increment, maximum and minimum values, starting point, automatic restarting capability and caching to improve performance.

The syntax of the ALTER SEQUECE statement is as follows:

ALTER SEQUENCE <Sequence Name>
 [ RESTART WITH <constant> ]
 [ INCREMENT BY <constant> ]
 [ { MINVALUE [ <constant> ] } | { NO MINVALUE } ]
 [ { MAXVALUE [ <constant> ] } | { NO MAXVALUE } ]
 [ CYCLE | { NO CYCLE } ]
 [ { CACHE [ <constant> ] } | { NO CACHE } ]

One difference in the syntax of the CREATE SEQUENCE and ALTER SEQUENCE is the absence of the “AS” argument in the ALTER SEQUENCE. Including the AS argument will generate this error message, as can be seen in the following script:

CREATE SEQUENCE [dbo].[SQLSequence] AS TINYINT
START WITH 1 INCREMENT BY 1
GO

ALTER SEQUENCE [dbo].[SQLSequence] AS INT
GO

Msg 11711, Level 15, State 1, Line 1
Argument 'AS' cannot be used in an ALTER SEQUENCE statement.

Solution / Work Around

If the purpose of including the AS argument in the ALTER SEQUENCE is to change the data type of the sequence object, then this is not allowed. To change the data type of an existing sequence object, the sequence object needs to be dropped first then created again.

DROP SEQUENCE [dbo].[SQLSequence]
GO

CREATE SEQUENCE [dbo].[SQLSequence] AS INT
START WITH 1 INCREMENT BY 1
GO

If the sequence is currently being used and the re-created sequence needs to restart from its current value, then the current value needs to be determined first using the NEXT VALUE FOR function:

SELECT NEXT VALUE FOR [dbo].[SQLSequence]

Once the current value has been retrieved, that value then is used as the starting value in the START WITH argument of the CREATE SEQUENCE statement:

CREATE SEQUENCE [dbo].[SQLSequence] AS INT
START WITH <Put Current Value Here> INCREMENT BY 1
GO