What is the true need of sequence in Sql Server 2012?

By : Tarik
Source: Stackoverflow.com
Question!

In SQL Server 2012 (Denali), we have a new feature Sequence. Consider the below program

/****** Create Sequence Object ******/
CREATE SEQUENCE GenerateSequence
START WITH 1
INCREMENT BY 1;

/****** Create a test Table ******/
Create TABLE tblTest
(
ID int NOT NULL PRIMARY KEY,
Name varchar(100) NOT NULL,
Age int NOT NULL
);

/****** Insert Some Records ******/
INSERT INTO tblTest(ID, Name, Age)
VALUES (NEXT VALUE FOR GenerateSequence, 'Name1',10),
(NEXT VALUE FOR GenerateSequence, 'Name2',20),
(NEXT VALUE FOR GenerateSequence, 'Name3',30),
(NEXT VALUE FOR GenerateSequence, 'Name4',40);

/****** Display result******/
SELECT * FROM tblTest;

Output

ID Name     Age
1  Name1    10
2  Name2    20
3  Name3    30
4  Name4    40

Instead of this we can use an identity column like

Create TABLE tblTest
(
ID int NOT NULL PRIMARY KEY Identity,
Name varchar(100) NOT NULL,
Age int NOT NULL
);

and can populate like

INSERT INTO tblTest(Name, Age)
VALUES ('Name1',10),
VALUES ('Name2',20)... 

etc. to get the same output

Then what extra benifit we will achieve by using sequence?

By : Tarik


Answers

This can be used between different database tables, so that it keeps the ID unique between multiple tables.

Have a look at SQL Sequences and Create Sequence of Numbers in SQL Server 2011

A SQL Server sequence object generates sequence of numbers just like an identity column in sql tables. But the advantage of sequence numbers is the sequence number object is not limited with single sql table.



This video can help you solving your question :)
By: admin