Modifying the SEQUENCE in SQL Server

Read – SEQUENCE in SQL Server
Read – Applying a SEQUENCE to an existing table.
Read – Find the last used SEQUENCE value

In this article, we will see how to modify the existing Sequence.

Example:
Creating a SEQUENCE Object.

The sequence TestSeq is created in the following example, and it starts with 1 and increases by 1 each time a number is generated. Because the sequence is set up to cycle, it resumes at the minimum value of 1 when the value falls below the maximum value of 200.

CREATE SEQUENCE TestSeq
AS SMALLINT
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 200
CYCLE 
CACHE 3

The TestSeq sequence is changed in the example below to have a range of 5 to 500. Every time a number is generated, the sequence resets the numbering series to 10 and increases it by 10.

ALTER SEQUENCE TestSeq
RESTART WITH 10
INCREMENT BY 10
MINVALUE 5
MAXVALUE 500
NO CYCLE
NO CACHE
;
GO

Hope you find this article helpful.

2 comments

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s