Sequences in Database Systems

Generating unique integers is a very common task in database systems. Many applications require each row in a given table to hold a unique value. One way to tackle this problem is to use sequences.

What are Sequences?

A sequence is a database object which allows users to generate unique integer values. The sequence is incremented every time a sequence number is generated. The incrementation occurs even if the transaction rolls back, which may result in gaps between numbers. Similarly, gaps may arise when two users increment the same sequence concurrently.

Example of Usage

To create a sequence, the following general statement can be used:

CREATE SEQUENCE sequence_name 
[MAXVALUE q | NOMAXVALUE] 
[MINVALUE v | NOMINVALUE]
[START WITH n]
[INCREMENT BY m]
[CACHE x | NOCACHE]
[CYCLE | NOCYCLE];

In the above statement, the MINVALUE and MAXVALUE options define the minimum and the maximum value which the sequence can generate, while the START WITH clause indicates the first sequence number to be generated. It is useful when the sequence is created in an already existing database and some initial values should not be generated. The START WITH value must fall into the range specified by MAXVALUE and MINVALUE.

The INCREMENT BY clause is used to specify the interval between consecutive sequence values. It can be any positive or negative integer different from 0. It is therefore possible to create a sequence object with, for example, decreasing values or even numbers only.

The CACHE option specifies how many values are preallocated and kept in memory for faster access. The larger the cache, the fewer times the database needs to update the root file. On the other hand, greater CACHE values may cause bigger gaps in numbering – if the application makes use of only a few sequence values during the session, the unused numbers will be discarded. The default value for this option varies from one database system to another. In PostgreSQL, for example, it equals 1 (i.e. no cache), whereas in Oracle it stores 20 sequence numbers by default and the minimum value specified by the user is 2.

The CYCLE clause indicates that the sequence should keep generating numbers after reaching the maximum/minimum value. Note that in such case the next number generated will be MINVALUE or MAXVALUE, respectively, and not the START WITH value. By default, most major database systems apply the NO CYCLE clause and return an error or throw an exception once the limit is reached.

Various database systems allow some additional clauses. Oracle specifies the [ ORDER | NOORDER ] clause which is used to guarantee that sequence numbers are generated in the exact order of request. This may come in handy when the sequence numbers are used as time stamps. In PostgreSQL, in turn, the [ OWNED BY { table_name.column_name | NONE } ] option associates the sequence with a specific table column. As a result, when the column is dropped, the sequence will be dropped as well.

Each database system specifies a set of functions to access the numbers generated by a sequence. In PostgreSQL, the nextval(sequence_name) function can be used to advance the sequence to its next value and return it, while the currval(sequence_name) function can be applied to retrieve the value most recently obtained. For instance, these functions can be used in SELECT clauses, as shown below.

SELECT nextval('clients_seq');

SQL Server offers the NEXT VALUE FOR sequence_name function to obtain the next sequence number and the sp_sequence_get_range function to get a range of values. The example statement changes slightly:

SELECT NEXT VALUE FOR clients_seq;

Oracle makes use of the so-called pseudocolumns – CURRVAL to return the current value of the sequence and NEXTVAL to increment the sequence and return the new value. Again, the SQL statement looks a bit differently:

SELECT clients_seq.nextval;

Sequences can be easily modified with ALTER SEQUENCE, as in the following example:

ALTER SEQUENCE products_sequence CYCLE CACHE 10;

The above statement will turn on the CYCLE option and change the number of cached values to 10. Similarly, the following will delete the sequence with a given name:

DROP SEQUENCE sequence_name;

Similar mechanisms

Sequences are supported by many major database systems, including PostgreSQL, SQL Server and Oracle. Other database systems may offer similar mechanisms. In MySQL, you can set the AUTO_INCREMENT attribute to a column to automatically generate subsequent values. The following rules must be followed:

• there can be only one AUTO_INCREMENT column in each table, with data type typically set to integer;
• the column with AUTO_INCREMENT must be indexed (either PRIMARY KEY or UNIQUE index);
• the column with AUTO_INCREMENT must have the NOT NULL constraint

To create a table with AUTO_INCREMENT you can use the following statement:

CREATE TABLE customers (
    customer_id INT(4) AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(32),
    last_name VARCHAR(64)
);

AUTO_INCREMENT will then generate subsequent primary key values for the column customer.

In SQL Server, a very similar mechanism called IDENTITY can be used in the following way:

CREATE TABLE customers (
    customer_id INT(4) IDENTITY(1,1) PRIMARY KEY,
    first_name VARCHAR(32),
    last_name VARCHAR(64)
);

The clause IDENTITY(1,1) informs the system to start from 1 and increment the value by 1.

For more information on the possibilities offered by your database system, refer to the documentation.