Applying SEQUENCE to tables in SQL Server

An object connected to a user-defined schema called a sequence produces a series of numerical numbers in accordance with the specification used to construct it. A sequence can be defined as a list of integers in an ordered fashion.

Read about Sequence in SQL Server here.

This article will demonstrate how a sequence can be used with the current tables.

The series EmpSeq is created in the following example.

Once it has been executed, a sequence based on the START value and incremented by the value supplied will be generated.

CREATE SEQUENCE EmpSeq
START WITH 1
INCREMENT BY 1
NO CYCLE;

SELECT NEXT VALUE FOR EmpSeq, * FROM Emp;

As you may have seen, it produced a sequence number that was increased by 1 for each row. The final row’s sequence is 20. However, if we use it again (by running the select statement a second time), the series will start from the last sequence generated, or from point 21, because no cycle option, maxvalue, or minvalue are supplied.

This slideshow requires JavaScript.

Let’s drop it and recreate it.

DROP SEQUENCE EmpSeq;

CREATE SEQUENCE EmpSeq
START WITH 1
INCREMENT BY 1
MAXVALUE 20
CYCLE;

The above statement will return 1 to 20 upon first execution. The sequence will produce negative values the following time because MINVALUE was not set.

So, let’s add MINVALUE as well and see what happens.

DROP SEQUENCE EmpSeq

CREATE SEQUENCE EmpSeq
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 20
CYCLE;

SELECT NEXT VALUE FOR EmpSeq, * FROM Emp;

Now that the sequence has been adjusted to meet our needs, it began with 1 and increased by 1. Since we specified the CYCLE option, it attempted to resume the sequence once it reached the maximum value. It searches for MINVALUE while restarting and uses MINVALUE as the START value. Now, no matter how many times we execute it, it always returns the same sequence number.