Sequence expressions return values from defined database sequences.

A sequence expression consists of one of two different operators: NEXT VALUE FOR or CURRENT VALUE FOR.

The NEXT VALUE operator returns the next available value from the referenced sequence:

The CURRENT VALUE operator returns the previous value returned from the sequence to the executing application:

Sequence expressions are typically used in INSERT or UPDATE statements to maintain columns that reflect an ordinal relationship with the creation of their containing rows. For example:

or

Sequence expressions can also be used in the select list of a SELECT statement. They cannot, however, be used in a WHERE, ON, GROUP BY, HAVING, ORDER BY, or UNION clause, in a subquery, or with aggregate functions.

A NEXT VALUE or CURRENT VALUE expression on a particular sequence is evaluated once per row inserted by an INSERT statement, updated by an UPDATE statement, or added to the result set of a SELECT statement. If several occurrences of a NEXT VALUE or CURRENT VALUE expression on the same sequence are coded in a single statement, only one value is computed for each row touched by the statement. If a NEXT VALUE expression and a CURRENT VALUE expression are coded on the same sequence in the same statement, the NEXT VALUE expression is evaluated first, then the CURRENT VALUE expression (assuring they return the same value), regardless of their order in the statement syntax.

Operand type: None

Result type: INTEGER8

Returns the last value generated for an identity column (see Identity Columns on page 491) in any table for the current session. If no identity value has been generated in the current session, returns NULL.

The number returned is specific to the current session, but not to a particular table.

In applications, sequences use logical locks that allow multiple transactions to retrieve and update the sequence value while preventing changes to the underlying sequence definition. The logical lock is held until the end of the transaction.