Sequences compared to identity columns

Although sequences and identity columns seem to serve similar purposes for Db2® applications, there is an important difference. An identity column automatically generates values for a column in a single table using the LOAD utility. A sequence generates sequential values upon request that can be used in any SQL statement using the CREATE SEQUENCE statement.

Identity columns

Allow the database manager to automatically generate a unique numeric value for each row that is added to the table. If you are creating a table and you know you need to uniquely identify each row that is added to that table, then you can add an identity column to the table definition as part of the CREATE TABLE statement:

    CREATE TABLE table_name
      (column_name_1 INT, 
       column_name_2, DOUBLE, 
       column_name_3 INT NOT NULL GENERATED ALWAYS AS IDENTITY
         (START WITH value_1, INCREMENT BY value_2))

In this example, the third column identifies the identity column. One of the attributes that you can define is the value used in the column to uniquely define each row when a row is added. The value following the INCREMENT BY clause shows by how much subsequent values of the identity column contents increase for every row added to the table.

After they are created, the identity properties can be changed or removed using the ALTER TABLE statement. You can also use the ALTER TABLE statement to add identity properties on other columns.

Sequences

Allow the automatic generation of values. Sequences are ideally suited to the task of generating unique key values. Applications can use sequences to avoid possible concurrency and performance problems resulting from the generation of a unique counter through other means. Unlike an identity column, a sequence is not tied to a particular table column, nor is it bound to a unique table column and only accessible through that table column.

A sequence can be created, and later altered, so that it generates values by incrementing or decrementing values either without a limit; or to a user-defined limit, and then stopping; or to a user-defined limit, then cycling back to the beginning and starting again.

The following example shows how to create a sequence called orderseq:

    CREATE SEQUENCE orderseq 
      START WITH 1 
      INCREMENT BY 1 
      NOMAXVALUE 
      NOCYCLE 
      CACHE 50

In this example, the sequence starts at 1 and increases by 1 with no upper limit. There is no reason to cycle back to the beginning and restart from 1 because there is no assigned upper limit. The CACHE parameter specifies the maximum number of sequence values that the database manager preallocates and keeps in memory.