ASA SQL Reference
  SQL Functions
    Alphabetical list of functions

GET_IDENTITY function [Miscellaneous]

Function 

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

Syntax 

GET_IDENTITY ( [ owner.] table-name [, num_to_alloc ],... )

Parameters 

num_to_allocate    Default is 1.

Usage 

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.

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

No COMMIT is required after executing get_identity, 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 get_identity, as in the example.

GET_IDENTITY is a non-deterministic function. Successive calls to GET_IDENTITY may return different values. The query optimizer does not cache the results of the GET_IDENTITY function.

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

Standards and compatibility 

• SQL/92    Vendor extension.

• SQL/99    Vendor extension.

• Sybase    Not supported by Adaptive Server Enterprise.

See also 

CREATE TABLE statement

ALTER TABLE statement

NUMBER function [Miscellaneous]

Example 

The following statement makes three calls to the GET_IDENTITY function:

SELECT GET_IDENTITY('T1'),
   GET_IDENTITY('T2',10),
   GET_IDENTITY('T3',5)