Sequence Object In SQL Server 2012

A sequence is a user defined, schema bound object that will generate a sequence of numeric values (in ascending or descending order) according to specification. Unlike identity columns, a sequence is created independent of any table. A few interesting differences between the two are,

• A Sequence object is independent of any table, whereas the Identity column property is table specific.
• Unlike Identity, you can generate a new sequence value before using it in an insert Statement.
• You can define both the minimum & maximum values, as well as cycling & cache size options for a sequence.
• Unlike Identity, a sequence object will increment its value only when it is explicitly called.

Let's create a sequence object and see how it works

CREATE SEQUENCE dbo.emp_sequence AS INT
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 10000
CACHE 20 CYCLE;

We have created a sequence with name emp_sequence where the starting value will be 1 and next value will be incremented by 1.

Now let us fetch first value for the sequence.

SELECT NEXT VALUE FOR dbo.emp_sequence as first_emp

Output


Check the output where first value is fetched as 1.

Now let us fetch next value for the sequence.

SELECT NEXT VALUE FOR dbo.emp_sequence as next_emp

Output



The next value is fetched as 2 which is incremented by 1 from the previous value.

Now let's see how a sequence object can be used with table and can act as alternative to identity.

For this, we are going to use the same sequence we created in the earlier example, so let us first reset the initial value of sequence.

ALTER SEQUENCE dbo.emp_sequence
RESTART WITH 1
INCREMENT BY 1;

Now let us create a temp table employee where we will insert the data in emp_no column using sequence,

CREATE TABLE #employee (emp_no int, emp_name VARCHAR(10));
INSERT INTO #employee VALUES
(NEXT VALUE FOR dbo.emp_sequence, 'Ajay')
, (NEXT VALUE FOR dbo.emp_sequence, 'Vijay')
, (NEXT VALUE FOR dbo.emp_sequence, 'Sanjay')

SELECT * FROM #employee

DROP TABLE #employee

Output



Check the output where emp_no is generated sequentially as 1, 2, 3 using sequence.

In the script we have also dropped the temp table, but the current value of sequence has been incremented to 3.

Let's run the same script again,

CREATE TABLE #employee (emp_no int, emp_name VARCHAR(10));
INSERT INTO #employee VALUES
(NEXT VALUE FOR dbo.emp_sequence, 'Ajay')
, (NEXT VALUE FOR dbo.emp_sequence, 'Vijay')
, (NEXT VALUE FOR dbo.emp_sequence, 'Sanjay')

SELECT * FROM #employee

DROP TABLE #employee

Output



Check the output where emp_no is generated as 4, 5, 6 using the same script. This is because the value of sequence was 3.