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:

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:

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:

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


Run the NEXT statement again:

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.