Sequence Object in SQL Server 2012

Introduction

A sequence is a user defined, schema bound object that generates 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

Background

Microsoft introduced sequence object in SQL Server 2012 release. 

Using the code

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

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 lets 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 lets 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 how can act as alternative to identity. 

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

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

Now lets 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
<span style="font-size: 9pt;">DROP TABLE #employee</span>

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.