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;