SEQUENCE in SQL Server 2012

SQL Server 2012 (or Denali) has now arrived CTP. In this
article I will look at a core new feature of SQL Server 2012 which is SEQUENCE.
Well, if you are familiar with Oracle, you will already know all about this
feature since it has been standard on Oracle more than 10 years I guess.

What is Sequence in SQL Server ?

In simple terms, it is a new database object and a substitute
for the Identity of columns.

Using the identity attribute for a column, you can easily
generate auto-incrementing numbers (which as often used as a primary key). With
Sequence, it will be a different object which you can attach to a table column
while inserting. Unlike identity, the next number for the column value will be retrieved
from memory rather than from the disk – this makes Sequence significantly
faster than Identity. We will see this in coming examples.

Creating a Sequence in SQL Server

To use Sequence first SQL Server Management Studio (SSMS)
and expand the Object explorer, under programmability you will see the sequence
node.

If you right click the sequence and select new, you will be
taken to the below screen which has all the attributes for the sequence.

Since Sequence is a database object, it needs to be assigned
to a schema. It has a data type which can be int, bigint, tinyint,
smallint,numeric or decimal. The start value and increment as similar as to the
values you will be familiar with using Identity.

The Minimum and maximum are boundaries for the sequence. When
 the cycle option is set you have the ability to re-use sequence numbers. 

Similarly, Sequences can be created using T-SQL as follows.

IF EXISTS (SELECT * FROM sys.sequences WHERE name =
N’EmployeeSeq’)
                DROP SEQUENCE EmployeeSeq;
GO

 CREATE SEQUENCE EmployeeSeq AS tinyint
                START WITH 0
                INCREMENT BY 5;
GO

Now let us see how we can integrate this with an Insert statement.

First we will create a table to incorporate the sequence we
created.

CREATE TABLE Employee

(ID tinyint,  Name varchar(150) )

Then we will insert:

INSERT INTO Employee
(ID,Name)
VALUES

(NEXT VALUE FOR EmployeeSeq, ‘Dinesh’)
INSERT INTO Employee
(ID,Name)

VALUES

(NEXT VALUE FOR EmployeeSeq, ‘Asanka’)

Note that you are now using the EmployeeSeq sequence object
for the insert.

Continues…

Pages: 1 2 3




Array

No comments yet... Be the first to leave a reply!