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. 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.

Creating a Sequence object

CREATE SEQUENCE MySequence

    AS INT

    START WITH 1

    INCREMENT BY 1

       MINVALUE 1

       MAXVALUE 10000

       CYCLE

       CACHE 50;

GO

 

Sequence exception

MSDN CREATE SEQUENCE (Transact-SQL)

See all articles by Arshad Ali

Arshad Ali
Arshad Ali
Arshad Ali works with Microsoft India R&D Pvt Ltd. He has 8+ years of experience, mostly on Microsoft Technologies. Most recently, as a SQL Developer and BI Developer he has been working on a Data Warehousing project. Arshad is an MCSD, MCITP: Business Intelligence, MCITP: Database Developer 2008 and MCITP: Database Administrator 2008 certified and has presented at several technical events including SQL-School. On an educational front, he has an MCA (Master in Computer Applications) and MBA-IT. Disclaimer : I work for Microsoft and help people and businesses make better use of technology to realize their full potential. The opinions mentioned herein are solely mine and do not reflect those of my current employer or previous employers.

Latest Articles