SQL Server 2012: DENALI series: SEQUENCE OBJECT

Hi Friends,

At Kolkata DevCon 2011, I was demonstrating SEQUENCE OBJECT, and one participant asked if SEQUENCE OBJECTS would respect a transaction. The answer is NO.

First, what is a SEQUENCE OBJECT?

From Books Online:

A sequence is a user-defined schema-bound object that generates a sequence of numeric values according to the specification with which the sequence was created. The sequence of numeric values is generated in an ascending or descending order at a defined interval and may cycle (repeat) as requested. Sequences, unlike identity columns, are not associated with tables. An application refers to a sequence object to receive its next value. The relationship between sequences and tables is controlled by the application. User applications can reference a sequence object and coordinate the values keys across multiple rows and tables.

From me 🙂

In simple words, its like an independent global generator of sequence numbers. Unlike IDENTITY, a SEQUENCE object is not associated with any table . However, the many aspects like seed, increment, start value, max value, etc can be controlled. The idea is, an application can control it and multiple table objects can use it.

Let us create a SEQUENCE object:

USE tempdb
GO
 
 
CREATE SEQUENCE dbo.TestID
AS INT
MINVALUE 1
NO MAXVALUE
START WITH 1;

The above code sets 1 as the minimum value for the sequence object, no max value and to start with 1. To generate the first and next numbers:

SELECT NEXT VALUE FOR dbo.TestID

And the output is:

Keep executing the NEXT statement and you shall see series of serial integer being generated.

Now, coming back to the question, will SEQUENCE respect transactions? Well, even if the next number is generated within a transaction and the transaction rolls back, the generation is not rolled back. Which means once the next number is generated (irrespective of an explicit transaction), it is permanent.

Execute the following:

-- what about transactions??
BEGIN TRAN
SELECT NEXT VALUE FOR dbo.TestID
 
ROLLBACK TRAN

You will observe the next number being generated, and the transaction rolled back.

Run the NEXT statement again:

SELECT NEXT VALUE FOR dbo.TestID

And you shall see the next value, which means the generation is permanent.

In my next post, I shall show how we can use the SEQUENCE object to insert data into tables.