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.
精彩评论