ASA SQL User's Guide
  Transact-SQL Compatibility
    Configuring databases for Transact-SQL compatibility

The special IDENTITY column

To create an IDENTITY column, use the following CREATE TABLE syntax:

CREATE TABLE table-name (
   ...
   column-name numeric(n,0) IDENTITY NOT NULL,
   ...
)

where n is large enough to hold the value of the maximum number of rows that may be inserted into the table.

The IDENTITY column stores sequential numbers, such as invoice numbers or employee numbers, which are automatically generated. The value of the IDENTITY column uniquely identifies each row in a table.

In Adaptive Server Enterprise, each table in a database can have one IDENTITY column. The data type must be numeric with scale zero, and the IDENTITY column should not allow nulls.

In Adaptive Server Anywhere, the IDENTITY column is a column default setting. You can explicitly insert values that are not part of the sequence into the column with an INSERT statement. Adaptive Server Enterprise does not allow INSERTs into identity columns unless the identity_insert option is on . In Adaptive Server Anywhere, you need to set the NOT NULL property yourself and ensure that only one column is an IDENTITY column. Adaptive Server Anywhere allows any numeric data type to be an IDENTITY column.

In Adaptive Server Anywhere the identity column and the AUTOINCREMENT default setting for a column are identical.

Retrieving IDENTITY column values with @@identity