Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted July 6, 2011

Understanding Sequence Object in SQL Server Denali

By Arshad Ali

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

 

NEXT VALUE FOR is a new function used to get the next sequence number from the sequence object. The sp_sequence_get_range system stored procedure returns a range of sequence numbers from the specified sequence object.

Using a Sequence object

CREATE TABLE DemoTable (col1 int, col2 varchar(50))

GO

INSERT DemoTable (col1, col2)

VALUES (NEXT VALUE FOR MySequence, 'Sequence object demo')

GO 100

SELECT * FROM DemoTable

 

You can query the sys.sequences catalog view to get different properties of sequence object along with its current value as shown below:

Checking current value and getting next value

--Getting sequence object's properties and current value

SELECT start_value, increment, minimum_value, maximum_value, current_value

FROM sys.sequences WHERE name = 'MySequence'

GO

--Getting next sequence number

SELECT NEXT VALUE FOR MySequence

 

When there is a need you can let sequence object restart either from MINVALUE or from specified value using the ALTER SEQUENCE command. If you don't specify any numeric value after RESTART it will start from the MINVALUE, or if you specify any value then it will start from there:

Resetting new seed value and checking current value

--Resetting the sequence object to start from 1

ALTER SEQUENCE MySequence

RESTART WITH 1;

--Getting sequence object's properties and current value

SELECT start_value, increment, minimum_value, maximum_value, current_value

FROM sys.sequences WHERE name = 'MySequence'

GO

 

You are not limited to using sequence object before or with DML statements, but you can also specify a column's default value to get a value from the sequence object as shown below:

Using sequence object as column default

--Utlizing sequence object as a column default

CREATE TABLE DemoTable2

(

       col1 int DEFAULT (NEXT VALUE FOR MySequence),

       col2 varchar(50))

GO

INSERT DemoTable2 (col2)

VALUES ('Sequence object demo')

GO 100

SELECT * FROM DemoTable2

GO

 

If sequence object reaches its limit (depending on the data type you have used, such as tinyint, smallint, int, etc.) and no recycle (CYCLE property) has been specified, asking for next value will give you an exception like this:

Msg 11728, Level 16, State 1, Line 1

The sequence object 'MySequence' has reached its minimum or maximum value. Restart the sequence object to allow new values to be generated.

Sequence exception

Figure 1 - Exception when sequence object reaches its limit

You are not bound to create and manage sequence objects using just T-SQL; SSMS has a new UI for this. Go to Databases -> Your database -> Programmability -> "Sequences" and you will get a list of all sequence objects created so far in the database. You can right click on the "Sequences" node and click on the "New Sequence" menu item to create a new sequence, or you can right click on the selected sequence object and click on the "Properties" menu item to edit it.

ssms sequence

Figure 2 - Creating sequence object using SSMS

When you click on "New Sequence" to create a new sequence object or click on the "Properties" menu item on the existing sequence to edit it, a screen similar to the one  below will be shown. Here you can specify and change different properties of the sequence object:

sequence properties

Figure 3 - Changing different properties for sequence object

Please note:

·         A sequence object is like any other user defined object and hence permission can be granted in the same way you grant permissions for other objects. 

·         Sequence objects are not transaction aware, which means when you use the NEXT VALUE FOR function in a transaction and then you rollback the transaction, it doesn’t mean it will give the same value again when you call the NEXT VALUE FOR function again but rather it will give the subsequent value irrespective of rollback. 

·         The sample code, example and UI is based on SQL Server Denali CTP 1; it might change in the final or RTM release.

Conclusion

In this article I talked about Sequence objects. A Sequence object is another user-defined schema-bound object type which provides functionality similar to the functionality provided by the Identity column. Sequence object generates the next sequential number as per the specification with which 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 or recycle numbers if it reaches the limit if you have specified it to recycle when creating the sequence object.

Resources

Download SQL Server Denali CTP 1
MSDN Creating and Using Sequence Numbers

MSDN CREATE SEQUENCE (Transact-SQL)

See all articles by Arshad Ali



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM