In this article I will discuss this new feature in SQL Server 2012 called Sequence and how we can use this feature.

Sequence in SQL Server 2012 is a database object and can be considered as an alternative to Identity property which we set at a table level. The primary difference between the two is that sequence is scoped to the entire database while Identity is scoped to a specific column in a specific table.

If we expand the programmability node under a specific database in SQL Server 2012 then the last object that we would find is Sequences.
The primary purpose of sequence is that it can used between different tables within the database and help maintain a synced copy of seed values between multiple tables that are referencing each other as it is not limited to a single table.
Another objective where Sequence can be very handy is performance gain because using identity means that SQL has to fetch the next value from the disk whereas sequences would be residing in the memory in case it is used with the cache option which we will see later.

Let us now understand with an example how we can create and implement sequence.

Creating a Sequence:
Step 1: Right click on the Sequence object under the Programmability node
Step 2: Select ‘New Sequence’ and you will see the below dialog box
Let us go through and understand each field in this dialog box before proceeding

a) Sequence name: This is where you give a name of the sequence. By default it picks up the date and time of the sequence creation.
b) Sequence schema: The schema to which you would assign the sequence.
c) Data type: The data type of the sequence. By default it is bigint.
d) Start Value: The starting value of the sequence
e) Increment By: The value by which you want to increment the series.
f) Minimum value: The least value of the series. This comes into play when you cycle the series.
g) Maximum value : The upper bound of the series post which the series will either cycle or stop.
h) Cycle : This property specifies whether the series should restart from the minimum value or throw an error when its minimum or maximum value is exceeded. The default cycle value for new sequence objects is NO CYCLE.
i) Cache: This increases performance by minimizing the number of disk IO that are required to generate sequence numbers. If a cache is set to 100 is chosen, SQL Server does not keep 100 individual values cached. It only caches the current value and the number of values left in the cache.

Step 3: Lets now fill up the dialog box and create our first sequence.
The TSQL for the same is given below

USE [AdventureWorks2012]
GO
CREATE SEQUENCE [dbo].[MySequence1]
AS [int]
START WITH 1000
INCREMENT BY 1
MAXVALUE 100000
CACHE
GO

When you click on ok button or you run the code, you will find a new sequence created under the Sequences node.

Now let us implement this object and understand how it works:
Step 1: Create a new table

USE [AdventureWorks2012]
GO
CREATE TABLE mytable
( ID INT PRIMARY KEY,
Fname VARCHAR(30),
Lname VARCHAR(30),
)
go

Step 2: Now lets insert some values into the table

INSERT INTO mytable
(ID,FName,Lname)
VALUES
(NEXT VALUE FOR Mysequence1, 'Sanchayan','Pandit')
INSERT INTO mytable
(ID,FName,Lname)
VALUES
(NEXT VALUE FOR Mysequence1, 'Abhishek','Agrawal')

Lets have a look at the rows inserted
As you observe now, the id field now has incremented by 1 starting with 1000.

So this is how we can implement Sequence in SQL Server 2012. I will deep dive into Sequence in my next article and we will understand how we can play around this new feature in SQL Server 2012.

Tagged: Cache, Cycle, Denali, identity and Sequence, New features in SQL Server 2012, Sequences, SQL Server 2012