Allocates values to an autoincrement column. This is an alternative to using autoincrement to generate numbers.

Syntax

GET_IDENTITY( table_name [, number_to_allocate ] )

Parameters

Returns

UNSIGNED BIGINT

Remarks

Using autoincrement or global autoincrement is still the most efficient way to generate IDs, but this function is provided as an alternative. The function assumes that the table has an autoincrement column defined. It returns the next available value that would be generated for the table's autoincrement column, and reserves that value so that no other connection will use it by default.

The function returns an error if the table is not found, and returns NULL if the table has no autoincrement column. If there is more than one autoincrement column, it uses the first one it finds.

number_to_allocate is the number of values to reserve. If number_to_allocate is greater than 1, the function also reserves the remaining values. The next allocation uses the current number plus the value of number_to_allocate. This allows the application to execute the GET_IDENTITY function less frequently.

No COMMIT is required after executing the GET_IDENTITY function, and so it can be called using the same connection that is used to insert rows. If ID values are required for several tables, they can be obtained using a single SELECT that includes multiple calls to the GET_IDENTITY function, as in the example.

The GET_IDENTITY function is non-deterministic function; successive calls to it may return different values. The optimizer does not cache the results of the GET_IDENTITY function.

For more information about non-deterministic functions, see Function caching.

See also

• CREATE TABLE statement
• ALTER TABLE statement
• NUMBER function [Miscellaneous]

Standards and compatibility

Example

The following statement returns the next available value for the Customers table autoincrement column (ID). The number returned and the following nine values are reserved:

SELECT GET_IDENTITY( 'Customers', 10 );