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. For example, the numbers returned would be integer; it will start from 1 through 10000 and will keep on incrementing with 1 each time the NEXT VALUE FOR function is called. The CYCLE property lets sequence object start all over again from MINVALUE if the MAXVALUE is reached.
For performance reasons, SQL Server pre-allocates the sequential numbers specified by the CACHE property. Default value for this property is 15, which means values 1 through 15 are made available from memory and the last cached value (15) is written to the system table on the disk. When the NEXT VALUE FOR function returns the last values, the next request (for number 16 through 30) will cause the cache to be reallocated and the last value (30) will be written to system table on the disk.