Comparison of identity columns and sequences

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 when the table is created or it can be added to a column using alter table. After a table is created, the identity column characteristics can be changed.
• 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.