Thursday, November 29, 2012

SQL Server 2012 | Sequence Feature


Sequence feature which has been in Oracle for a number of years is now available in MS SQL 2012! A Sequence object is an object that provides functionality similar to Identity (Autonumber) column. The sequence object can be used with more than one table which is not possible in identity object. This is useful when you have parent-child tables and you want to know the value of the ID column before you insert records.

A sample example is as follows:

Sample 1:
create sequence dbsequence
start with 1 increment by 5
select next value for dbsequence
--output=1

select next value for dbsequence
--output=6

1.1   Should you need to clean up …
drop sequence dbsequence

Sample 2.1:
create sequence idsequence
start with 1 increment by 3

create table Products_ext
(
id int,
Name varchar(50)
)

INSERT dbo.Products_ext (Id, Name)
VALUES (NEXT VALUE FOR dbo.idsequence, 'ProductItem1')


INSERT dbo.Products_ext (Id, Name)
VALUES (NEXT VALUE FOR dbo.idsequence, 'ProductItem2')


2.2 Check the result  …

select * from Products_ext

/* Output

1              ProductItem1
4              ProductItem2

*/

2.3 Should you need to clean up …

drop table Products_ext
drop sequence idsequence

No comments:

Post a Comment