Determining when to use identity columns or sequences

Although there are similarities between identity columns and sequences, there are also differences. The characteristics of each can be used when designing your database and applications.

Depending on your database design and the applications using the database, the following characteristics will assist you in determining when to use identity columns and when to use sequences.

Identity column characteristics

• 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 during the modification of the contents of the table.
• After inserting a row, the generated identity value can be retrieved either by using the IDENTITY_VAL_LOCAL() function or by selecting the identity column back from the insert by using the SELECT FROM INSERT statement.
• The LOAD utility can generate IDENTITY values.

Sequence characteristics

• Sequences are not tied to any one table.
• Sequences generate sequential values that can be used in any SQL or XQuery statement.

  Since sequences can be used by any application, there are two expressions used to control the retrieval of the next value in the specified sequence and the value generated previous to the statement being executed. 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 and XQuery statements within several tables.