Using a sequence to generate unique values

You can use a sequence to generate values that are unique across multiple tables or that are different from a set of natural numbers. A sequence is created using the CREATE SEQUENCE statement. Sequence values are returned as BIGINT values.

For each connection, the most recent use of the next value is saved as the current value.

When you create a sequence, its definition includes the number of sequence values the database server holds in memory. When this cache is exhausted, the sequence cache is repopulated. If the database server fails, then sequence values that were held in the cache may be skipped.

Obtaining values in a sequence

You must have DBA authority, be the owner of the sequence, or have been granted permission to use the sequence to execute these statements. For more information, see sequence-expression clause, SELECT statement.

Choosing between sequences and autoincrement values

Autoincrement behavior	Sequence behavior
Defined for a single column in a table	Stored as a database object and can be used anywhere that an expression is allowed
Column must have an integer data type or an exact numeric data type	Values can be referred to anywhere that an expression can be used and do not have to conform to default value for a column
Values can only be used for a single column in one table	Values can be used across multiple tables
Values are part of the set of natural numbers (1, 2, 3, ...)	Can generate values other than the set of natural numbers
Values must increment	Values can increment or decrement
A unique value that is one greater than the previous maximum value in the column is generated by default The sa_reset_identity system procedure can be used to change the autoincrement value for the next row that will be inserted	Unit of increment can be specified
If the next value to be generated exceeds the maximum value that can be stored in the column, NULL is returned	Can choose to allow values to be generated after the maximum or minimum value is reached, or return an error by specifying NO CYCLE

Sequence example

Consider a sequence that is used to generate incident numbers for a customer hotline. Suppose that customers can call in with two different types of complaints: incorrect billing or missing shipments.

CREATE SEQUENCE incidentSequence
   MINVALUE 1000
   MAXVALUE 100000;

CREATE TABLE reportedBillingMistake( 
   incidentID INT PRIMARY KEY DEFAULT (incidentSequence.nextval),
   billNumber INT,
   valueOnBill NUMERIC(10,2),
   expectedValue NUMERIC(10,2),
   comments LONG VARCHAR );

CREATE TABLE reportedMissingShipment( 
   incidentID INT PRIMARY KEY DEFAULT(incidentSequence.nextval),
   orderNumber INT,
   comments LONG VARCHAR );

Using incidentSequence.nextval for the incidentID columns guarantees that incidentIDs are unique across the two tables. This means that when a customer calls back for further inquiries and provides an incident value, there is no possibility of confusion as to whether the incident is a billing or shipping shipment.

To insert a billing mistake, the following statements would be equivalent:

INSERT INTO reportedBillingMistake VALUES( DEFAULT, 12345, 100.00, 75.00, 'Bad bill' );

INSERT INTO reportedBillingMistake
   SELECT incidentSequence.nextval, 12345, 100.00, 75.00, 'Bad bill';

To find the incidentID that was just inserted, the connection that performed the insert (using either of the above two statements) could issue the following statement:

SELECT incidentSequence.currval;