SQL Server Portal

‘How to change the datatype of type Sequence object’ became critical for me when I created a sequence object with a wrong datatype and deployed it on client side and later I tried to change the datatype of the sequence object but I could not 😦

Let me demonstrate the issue and its resolution step by step.

Step 1 :
In this step, we will create a sequence object using smallint data type.

Use AdventureWorks2012
GO
CREATE SEQUENCE [dbo].[Seq_Sample]
AS [SMALLINT]
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 20
NO CACHE
GO

Step 2 :
In this step, we will use 3 sequence IDs. This step is just for demonstration purpose only.

Use AdventureWorks2012
GO
SELECT NEXT VALUE FOR [Seq_Sample]
GO 3

Step 3 :
Let’s try to alter its datatype. This step is just to demonstrate the error only.

Use AdventureWorks2012
GO
ALTER SEQUENCE [dbo].[Seq_Sample] AS INT
GO
--OUTPUT

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

Ooopsss cannot change it. So, what is the solution ?

Step 4:
In this step, we need to get the current value (last value) of the above sequence object. Never skip this step because once you drop the above sequence object, it would be very difficult to get the current value if it is implemented for multiple tables.
Given below is the script.

Use AdventureWorks2012
GO
SELECT current_value FROM SYS.SEQUENCES
WHERE name='Seq_Sample'
--OUTPUT

current_value
————-
3

(1 row(s) affected)

Step 5:
Once you have the current value of the above sequence, it is time to drop the sequence object.

Use AdventureWorks2012
GO
DROP SEQUENCE [dbo].[Seq_Sample]
GO

Step 6:
Now, it is time to re-create the sequence object.  Make sure that you change the datatype of the sequence object. Also you must change the START value of the new sequence object’s to the current value + 1 (we got the current value in step 3).
Given below is the script.

Use AdventureWorks2012
GO
CREATE SEQUENCE [dbo].[Seq_Sample]
AS [INT]
START WITH 4
INCREMENT BY 1
MINVALUE 1
MAXVALUE 20
NO CACHE
GO

Step 7:
Finally, check the next value of the sequence.
Given below is the script.

Use AdventureWorks2012
GO
Select NEXT VALUE FOR [Seq_Sample] AS [Result]
GO
--OUTPUT

Result
———–
4

(1 row(s) affected)

Read Full Post »