New feature in SQL Server 2012 - Sequence

use Nwind

/*

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 before you insert records 

http://msdn.microsoft.com/en-us/library/ff878091.aspx

We are going to look at two examples:

-One with regular sequence function
-Second with using sequence in a table

*/
--Example 1

--run together

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

select next value for dbsequence
--output=6


--run seperate

drop sequence dbsequence




--Example 2, with a table 

--run together

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')


--run seperate

select * from Products_ext

/* Output

1ProductItem1
4ProductItem2

*/

drop table Products_ext
drop sequence idsequence