SQL — Sequences/Auto Increments

Sequences

A sequence is a database object that generates numbers in sequential order. Applications most often use these numbers when they require a unique value in a table such as primary key values. Some database management systems use an "auto number" concept or "auto increment" setting on numeric column types. Both the auto numbering columns and sequences provide a unique number in sequence used for a unique identifier. The following list describes the characteristics of sequences:

  • Sequences are available to all users of the database

  • Sequences are created using SQL statements (see below)

  • Sequences have a minimum and maximum value (the defaults are minimum=0 and maximum=263-1); they can be dropped, but not reset

  • Once a sequence returns a value, the sequence can never return that same value

  • While sequence values are not tied to any particular table, a sequence is usually used to generate values for only one table

  • Sequences increment by an amount specified when created (the default is 1)

Creating a Sequence

To create sequences, execute a CREATE SEQUENCE statement in the same way as an UPDATE or INSERT statement. The sequence information is stored in a data dictionary file in the same location as the rest of the data dictionary files for the database. If the data dictionary file does not exist, the SQL engine creates the file when it creates the first sequence. In legacy dictionaries, the new file name is SEQUENCE.DD. The format of this file remains proprietary and subject to change, so do not depend on the record layout or format of the data. In Journaled Filesystem databases, this information is also proprietary and subject to change.

The format for a CREATE SEQUENCE statement is as follows:

CREATE SEQUENCE sequence_name

 [INCREMENT BY #]

 [START WITH #]

 [MAXVALUE # | NOMAXVALUE]

 [MINVALUE # | NOMINVALUE]

 [CYCLE | NOCYCLE]

Variable

Description

INCREMENT BY

The increment value. This can be a positive or negative number.

START WITH

The start value for the sequence.

MAXVALUE

The maximum value that the sequence can generate. If specifying NOMAXVALUE, the maximum value is 263-1.

MINVALUE

The minimum value that the sequence can generate. If specifying NOMINVALUE, the minimum value is -263.

CYCLE

Specify CYCLE to indicate that when the maximum value is reached the sequence starts over again at the start value. Specify NOCYCLE to generate an error upon reaching the maximum value.

Dropping a Sequence

To drop a sequence, execute a DROP SEQUENCE statement. Use this function when a sequence is no longer useful, or to reset a sequence to an older number. To reset a sequence, first drop the sequence and then recreate it.

Drop a sequence following this format:

DROP SEQUENCE my_sequence

Using a Sequence

Use sequences when an application requires a unique identifier. INSERT statements, and occasionally UPDATE statements, are the most common places to use sequences. Two "functions" are available on sequences:

NEXTVAL: Returns the next value from the sequence.

CURVAL: Returns the value from the last call to NEXTVAL by the current user during the current connection. For example, if User A calls NEXTVAL and it returns 124, and User B immediately calls NEXTVAL getting 125, User A will get 124 when calling CURVAL, while User B will get 125 while calling CURVAL. It is important to understand the connection between the sequence value and a particular connection to the database. The user cannot call CURVAL until making a call to NEXTVAL at least once on the connection. CURVAL returns the current value returned from the sequence on the current connection, not the current value of the sequence.

Examples

To create the sequence:
CREATE SEQUENCE customer_seq INCREMENT BY 1 START WITH 100

To use the sequence to enter a record into the database:
INSERT INTO customer (cust_num, name, address)
VALUES (customer_seq.NEXTVAL,'John Doe','123 Main St.')

To find the value just entered into the database:
SELECT customer_seq.CURVAL AS LAST_CUST_NUM