SQL Server Denali Sequence Object Overview

Database Journal - Understanding Sequence Object in SQL Server Denali

"Until SQL Server 2008 R2, the Identity column was used to generate sequential numbers to identify records in a table, mostly used as a primary/foreign key. The scope of the identity column is the table on which it has been created, and the next sequential number is created when the DML statement is executed. But what if you want to have sequential generation of numbers across tables (instead of tying the numbers with just one table), and you want to have the next sequence number even before execution of the DML statement? SQL Server Denali has a new feature called Sequence object for these purposes, while retaining Identity column functionality too.

As in prior versions, there are some alternatives of Sequence object what are also in SQL Server Denali, but SQL Server Denali now natively supports it. To learn more about these alternatives, click here.

Understanding sequence objects

A Sequence object is another user-defined schema-bound object type that provides functionality similar to Identity column with some differences. Unlike Identity column, the sequence object can be used with more than one table; it also provides the next sequence number before actual DML execution. Getting next sequential number comes handy in a couple of scenarios, like when you have parent-child tables (such as Order and OrderDetail) and you want to insert records in these tables, want values for primary/foreign key, when you want to recycle the numbers, when you want to have the next number before actual DML, among other examples.

Sequence object generates the next sequential number as per the specification with which the sequence object has been created. The next sequential number could be either in ascending or descending order at a defined interval, as per specification. Sequence object may reuse/recycle numbers if it reaches the limit, if you have specified to recycle while creating the sequence object.

Getting started with Sequence Objects

As I said before, sequence object is like any other user defined schema bound object, and hence SQL Server has the CREATE/ALTER/DROP SEQUENCE command for managing these objects. You can query the sys.sequences catalog view to learn more about sequence objects in the database or you can view it in SSMS (discussed later in this article).

The below example creates a new sequence object and specifies its different properties....

"

I know I'm weird, but the Sequence object coming in Denali has me kind of excited. This solves a problem I have today, in a way that's usable, supported, baked into the box and easy to understand. Now all I need is Denali to ship and get it deployed...