Skip to content
Home ยป SQL Server Sequence

SQL Server Sequence

SQL Server Sequence objects are used to sequentially generate numeric values that can be in ascending or descending order at a defined interval and may cycle if requested.




Sequence objects are similar to the IDENTITY column in any SQL table. However, unlike the IDENTITY column, they are independent and are not attached to any table.

Sequence objects are used both independently and within the DML statements that is INSERT, UPDATE and DELETE.

SYNTAX

CREATE SEQUENCE [schema].[Sequencename]

ย ย [ AS <data type> ]

ย ย [ START WITH <value> ]

ย ย [ INCREMENT BY <value> ]

ย ย [ MINVALUE <value > | NO MINVALUE ]

ย ย [ MAXVALUE <value> | NO MAXVALUE ]

ย ย [ CYCLE | NO CYCLE ]

ย ย [ CACHE value | NO CACHE ];

CREATE SEQUENCEย  Used to create a sequence followed by a database schema and the name of the sequence.

AS is the default value for the data type is BigInt , Data types can be Int, SmallInt, TinyInt, BigInt and Decimal, .

START WITH is a starting value that the sequence returns initially.

INCREMENT BY it can be either a positive or negative value. If a positive value is specified, the sequence will be an ascending sequence of values. If a negative value is specified, the sequence will be a descending sequence of values.

MINVALUEย  is the minimum value for the sequence object.

NO MINVALUEย  means that no minimum value specified for the sequence object.

MAXVALUE ย is an optional parameter that sets the maximum value for the sequence object.

NO MAXVALUEย  means that no maximum value specified for the sequence object.

CYCLE ย sequence will start over once it has completed the sequence.

NO CYCLEย  It will not start the sequence over again.

CACHE is used to cache sequence object values.

NO CACHEย  It does not cache the sequence Objects.

Lets look at an example of Sequences in SQL.

Creating a Sequence

Following Statement create a sequence object named GenSeqId. The type of this sequence object is integer.

Sequence Objects starts from 100 and increments by 2.

CREATE SEQUENCEย dbo.GenSeqId
AS INT
START WITHย 100
INCREMENT BYย 2

You can check the details of a sequence using following statement.

SELECT *
FROM sys.sequences
WHERE name = 'GenSeqId';

To see the value that GenSeqId sequence object contains , use the following statement.




SELECT NEXT VALUE FOR [dbo].[GenSeqId]
Create table EmpData(
EmpId int ,
EmpName varchar(100)
)

Using a Sequences with Insert statement

Lets create a table EmpData.
Now we insert some records in the EmpData table, For the EmpId column of the table we use the following statement
INSERT INTO EmpData VALUES ( NEXT VALUE FOR [dbo].[GenSeqId],'NICK ANTHONY')

INSERT INTO EmpData VALUES ( NEXT VALUE FOR [dbo].[GenSeqId],'JACK MARK A')

 

Lets select the records , As you can see the output that the value for EmpId column is generated by the GenSeqId sequenceย  object that is 102 and 104 .

SELECT * FROM EmpData

Modifying a sequence objects

To modify an existing sequence, the ALTER SEQUENCE statement is used.

The following statement modifies an existing sequence object โ€˜GenSeqIdโ€™ by updating its starting value to 100

ALTER SEQUENCE [GenSeqId]
RESTART WITH 1

Lets check the value that GenSeqId sequence object holds now , as you can see it is 1, that means sequence objects value is updated.
SELECT NEXT VALUE FOR [dbo].[GenSeqId]

Sequenceย with Max and Min Values

Lets alter the GenSeqNumber and set min and Max values for it.



CREATE SEQUENCE [dbo].[GenSeqNumber]
AS INT
START WITH 1
INCREMENT BY 2
MINVALUE 1
MAXVALUE 6

As you can see above, we have created a sequence object named ‘GenSeqNumber‘, and it has a starting value that is incremented by 2 and the minimum value for this sequence object is 1 and maximum value is 6.

Now if you increment the value of this sequence object beyond 6, It returns an error.

As the maximum value for sequence object is set to 6, so when sequence object is executed for 3 time the value will be exceeding the maximum value 6 then it gives an error.

Lets execute theย  below statement for 3 three times and you can see it gives an error.

Select NEXT VALUE FOR [dbo].[GenSeqNumber]

 

Sequence with CYCLE

As you see above, when increment value of a sequence object go beyond to its maximum value, it gives an error, in case this case to avoid error , you can use a CYCLE flag.



If a CYCLE flag for a sequence is set to true, the value for the sequence object is again set to its starting value.

CREATE SEQUENCE [dbo].[GenSeqNumber]

AS INT

START WITH 1

INCREMENT BY 2

MINVALUE 1

MAXVALUE 6

CYCLE

When Sequence objected is executed and exceeds it’s maximum value the sequence value is reset to start value again.

Drop Sequence

To drop a sequence objects, you can use drop statement as given below.

Drop sequence [GenSeqNumber_withcycle]
Also Read..

SQL Server CTE (Common Table Expression)

SQL Recursive CTE

SQL Basics TutorialSQL Advance TutorialSSRSInterview Q & A
SQL Create tableSQL Server Stored ProcedureCreate a New SSRS Project List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLESQL Server MergeCreate a Shared Data Source in SSRSSQL Server Question & Answer Quiz
SQL DropSQL Server PivotCreate a SSRS Tabular Report / Detail Report
..... More.... More....More
Power BI TutorialAzure TutorialPython TutorialSQL Server Tips & Tricks
Download and Install Power BI DesktopCreate an Azure storage accountLearn Python & ML Step by stepEnable Dark theme in SQL Server Management studio
Connect Power BI to SQL ServerUpload files to Azure storage containerSQL Server Template Explorer
Create Report ToolTip Pages in Power BICreate Azure SQL Database ServerDisplaying line numbers in Query Editor Window
....More....More....More




 2,006 total views,  1 views today

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.