Using IDENTITY columns

An IDENTITY column contains a value for each row, generated automatically by Adaptive Server, that uniquely identifies the row within the table.

Each table can have only one IDENTITY column. You can define an IDENTITY column when you create a table with a create table or select into statement, or add it later with an alter table statement.

Define an IDENTITY column by specifying the keyword identity, instead of null or not null, in the create table statement. IDENTITY columns must have a datatype of numeric and scale of 0, or any integer type. Define the IDENTITY column with any desired precision, from 1 to 38 digits, in a new table:

create table table_name 
    (column_name numeric(precision ,0) identity)

The maximum possible column value is 10 ^precision - 1. For example, this command creates a table with an IDENTITY column that allows a maximum value of 10⁵ - 1, or 9999:

create table sales_daily
    (sale_id numeric(5,0) identity, 
    stor_id char(4) not null)

Once an IDENTITY column reaches its maximum value, all further insert statements return an error that aborts the current transaction

You can create automatic IDENTITY columns by using the auto identity database option and the size of auto identity configuration parameter. To include IDENTITY columns in nonunique indexes, use the identity in nonunique index database option.