DENALI TSQL – New Feature | SEQUENCES
As per MS BOL a Sequence is a user-defined schema-bound object that generates a sequence of numeric values according to the specification with which the sequence was created. The sequence of numeric values is generated in an ascending or descending order at a defined interval and may cycle (repeat) as requested.
--// Create a simple Sequence CREATE SEQUENCE CountBy1 START WITH 1 INCREMENT BY 1 ; GO -- Assign new ID sorted by First Name: SELECT BusinessEntityID, FirstName, MiddleName, LastName, NEXT VALUE FOR CountBy1 OVER(ORDER BY FirstName) as New_ID FROM Person.Person; GO -- If you run the above query again, it won't start from 1 but starts after the max value it ended in its first run. --// Create a Cyclic Sequence CREATE SEQUENCE CountBy5 AS tinyint START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 5 CYCLE ; GO -- Assign Persons to 5 different sections: SELECT BusinessEntityID, FirstName, MiddleName, LastName, NEXT VALUE FOR CountBy5 OVER(ORDER BY FirstName, LastName) as New_Section FROM Person.Person; GO --// Final cleanup DROP SEQUENCE CountBy1 DROP SEQUENCE CountBy5 GO