NEXT VALUE FOR Function in SQL2012

NEXT VALUE FOR Function

It is one of the new function in 2012. It can be used in stored procedures and triggers.
When the NEXT VALUE FOR function is used in a query or default constraint, if the same sequence object is used more than once, or if the same sequence object is used both in the statement supplying the values, and in a default constraint being executed, the same value will be returned for all columns referencing the same sequence within a row in the result set.

Syntax:

NEXT VALUE FOR [ database_name . ] [ schema_name . ] sequence_name
[ OVER (<over_order_by_clause>) ]

Examples:

CREATE SEQUENCE txtSequence
START WITH 100
INCREMENT BY 5;

SELECT
NEXT VALUE FOR txtSequence OVER(ORder BY EName) AS ENo,
EName FROM tblEmployee

Example for creates a table named tblEmployee and then uses the NEXT VALUE FOR function to insert a row

CREATE TABLE tblTest (ProductId int PRIMARY KEY,ProductName varchar(20) );

INSERT
tblTest(ProductId, ProductName) VALUES(NEXT VALUE FOR txtSequence, 'bike');

Suppose I am going to use SELECT ...... INTO statement to create a table named tblProduct

SELECT NEXT VALUE FOR txtSequence AS Id, ProductName INTO tblProduct
FROM tblTest;