SQL Server 2012 (Denali) New Features for Developers (Programming Enhancements) - Sequences

Sequences:

Sequence is a user defined object that generates a sequence of a number. Here is an example using Sequence. It operates similar to an identity column, but sequence numbers are not restricted to use in a single table.

How to create Sequence:

/****** Create Sequence Object ******/
CREATE SEQUENCE SequenceObj
START WITH 1
INCREMENT BY 1;

/****** Create Temp Table ******/
DECLARE @Person TABLE
(
ID int NOT NULL PRIMARY KEY,
FullName nvarchar(100) NOT NULL
);

How to get Next Value For Sequence:

/****** Insert Some Data ******/
INSERT @Person (ID, FullName)
VALUES (NEXT VALUE FOR SequenceObj, 'Brian Lauber'),
(NEXT VALUE FOR MySequence, 'Joel Winstead'),
(NEXT VALUE FOR MySequence, 'Patrick Murphy');
/****** Show the Data ******/
SELECT * FROM @Person;
The results would look like this:
ID FullName
1 Brian Lauber

2 Joel Winstead

3 Patrick Murphy

  

Viewing Details About Sequences on Your SQL Server

You can query SQL Server to show the sequences that are in a database.

SELECT * FROM sys.sequences 

When to use Sequence vs Identity Columns

Use sequences instead of identity columns in the following scenarios:

1. The application requires a number before the insert into the table is made.
2. The application requires sharing a single series of numbers between multiple tables or multiple columns within a table.
3. The application must restart the number series when a specified number is reached. For example, after assigning values 1 through 10, the application starts assigning values 1 through 10 again.
4. The application requires sequence values to be sorted by another field. The NEXT VALUE FOR function can apply the OVER clause to the function call. The OVER clause guarantees that the values returned are generated in the order of the OVER clause's ORDER BY clause.
5. An application requires multiple numbers to be assigned at the same time. For example, an application needs to reserve five sequential numbers. Requesting identity values could result in gaps in the series if other processes were simultaneously issued numbers. Calling sp_sequence_get_range can retrieve several numbers in the sequence at once.
6. You need to change the specification of the sequence, such as the increment value.

Sequence Limitations:
Unlike identity columns, whose values cannot be changed, sequence values are not automatically protected after insertion into the table. To prevent sequence values from being changed, use an update trigger on the table to roll back changes.

Uniqueness is not automatically enforced for sequence values. The ability to reuse sequence values is by design. If sequence values in a table are required to be unique, create a unique index on the column. If sequence values in a table are required to be unique throughout a group of tables, create triggers to prevent duplicates caused by update statements or sequence number cycling.

The sequence object generates numbers according to its definition, but the sequence object does not control how the numbers are used. Sequence numbers inserted into a table can have gaps when a transaction is rolled back, when a sequence object is shared by multiple tables, or when sequence numbers are allocated without using them in tables. When created with the CACHE option, an unexpected shutdown, such as a power failure, can lose the sequence numbers in the cache.


More on Sequences:
http://msdn.microsoft.com/en-us/library/ff878058%28v=sql.110%29.aspx