Comparison of identity columns and sequences

 

While identity columns and sequences are similar in many ways, there are also differences.

Examine these differences before you decide which to use.

An identity column has the following characteristics:

• An identity column can be defined as part of a table only when the table is created. After a table is created, you cannot alter it to add an identity column. (However, existing identity column characteristics can be altered.)

• An identity column automatically generates values for a single table.

• When an identity column is defined as GENERATED ALWAYS, the values used are always generated by the database manager. Applications are not allowed to provide their own values when changing the contents of the table.

• The IDENTITY_VAL_LOCAL function can be used to see the most recently assigned value for an identity column.

A sequence has the following characteristics:

• A sequence is a system object of type *DTAARA that is not tied to a table.

• A sequence generates sequential values that can be used in any SQL statement.

• There are two expressions used to retrieve the next values in the sequence and to look at the previous value assigned for the sequence. The PREVIOUS VALUE expression returns the most recently generated value for the specified sequence for a previous statement within the current session. The NEXT VALUE expression returns the next value for the specified sequence. The use of these expressions allows the same value to be used across several SQL statements within several tables.

While these are not all of the characteristics of identity columns and sequences, these characteristics can help you determine which to use depending on your database design and the applications that use the database.

 

Parent topic:

Creating and using sequences

 

Related reference

Creating and altering an identity column