ASA SQL User's Guide
Ensuring Data Integrity
Using column defaults
The 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 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 Enforcing entity integrity). 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.
While using the autoincrement default in other cases is possible, doing so can adversely affect database performance. For example, in cases where the next value for each column is stored as an integer (4 bytes), using values greater than 231 - 1 or large double or numeric values may cause wraparound to negative values.
You can retrieve the most recent value inserted into an autoincrement column using the @@identity global variable. For more information, see @@identity global variable.
Autoincrement is intended to work with positive integers.
The initial autoincrement value is set to 0 when the table is created. This value remains as the highest value assigned when inserts are done that explicitly insert negative values into the column. An insert where no value is supplied causes the AUTOINCREMENT to generate a value of 1, forcing any other generated values to be positive.
In UltraLite applications, the autoincrement value is not set to 0 when the table is created, and AUTOINCREMENT generates negative numbers when a signed data type is used for the column.
You should define AUTOINCREMENT columns as unsigned to prevent negative values from being used.
A column with the AUTOINCREMENT default is referred to in Transact-SQL applications as an IDENTITY column. For information on IDENTITY columns, see The special IDENTITY column.