The IDENTITY/AUTOINCREMENT default is useful for numeric data fields where the value of the number itself may have no meaning. The feature assigns each new row a value of one greater than the previous highest value in the column. You can use IDENTITY/AUTOINCREMENT columns to record purchase order numbers, to identify customer service calls or other entries where an identifying number is required.
Autoincrement columns are typically primary key columns or columns constrained to hold unique values (see CREATE TABLE statement in Chapter 1, “SQL Statements,” of Reference: Statements and Options). For example, autoincrement default is effective when the column is the first column of an index, because the server uses an index or key definition to find the highest value.
You can sometimes retrieve the most recent value inserted into an autoincrement column using the @@identity global variable. For more information, see Chapter 2, “SQL Language Elements,” in Reference: Building Blocks, Tables, and Procedures.
Sybase IQ does not support the special value GLOBAL AUTOINCREMENT.
IDENTITY/AUTOINCREMENT is intended to work with positive integers.
The initial IDENTITY/AUTOINCREMENT value is set to 0 when the table is created.
A column with the AUTOINCREMENT default is referred to in Transact-SQL applications as an IDENTITY column. Sybase IQ supports both keywords for compatibility.
