Fix “Argument ‘AS’ cannot be used in an ALTER SEQUENCE statement.” (Error Msg 11711) in SQL Server

If you’re getting an error that reads “Argument ‘AS’ cannot be used in an ALTER SEQUENCE statement.” in SQL Server, it’s probably because you’re trying to change the data type of a sequence object.

We can’t actually change the data type of a sequence object, so this error is to be expected.

If you need to change the data type of a sequence object, you’ll need to drop the sequence and recreate it with the correct data type.

Example of Error

Here’s an example of code that produces the error:

ALTER SEQUENCE sequence1 AS tinyint;

Result:

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

In this example, I tried to change the data type by using the AS argument but I got an error.

The error message is quite self-explanatory – we can’t use the AS argument in an ALTER SEQUENCE statement.

The CREATE SEQUENCE statement on the other hand, accepts the AS argument. In the CREATE SEQUENCE statement, the AS argument allows us to specify a data type for the sequence, but once we’ve created the sequence, we can’t change the data type. Therefore, there’s no provision for this in the ALTER SEQUENCE statement.

Solution

To change the data type of a sequence object, we need to drop the sequence then create it again, with the correct data type.

Example:

DROP SEQUENCE sequence1;
CREATE SEQUENCE sequence1 AS tinyint;

This example drops the sequence then creates it again using a tinyint data type. In this example, all properties will use the default values (because I didn’t specify any in my statement). Therefore, properties such as START WITH, MINVALUE and MAXVALUE will be based on this data type.

Here’s an example that includes setting some properties:

DROP SEQUENCE sequence1;
CREATE SEQUENCE sequence1 
    AS tinyint
    START WITH 10
    INCREMENT BY 1
    MINVALUE 1
    MAXVALUE 100
    CYCLE;