开发者

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

开发者 https://www.devze.com 2023-01-29 09:05 出处:网络
In SQL Server 2012 (Denali), we have a new feature Sequence. Consider the below program /****** Create Sequence Object ******/

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?


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.

0

精彩评论

暂无评论...
验证码 换一张
取 消