IDENTITY Columns Usage

An IDENTITY column contains a value for each row, generated automatically by SAP ASE, 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.

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

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.

• Create IDENTITY Columns with User-Defined Datatypes
  You can use user-defined datatypes to create IDENTITY columns. The user-defined datatype must have an underlying type of numeric and a scale of 0, or any integer type.
• Reference IDENTITY Columns
  When you create a table column that references an IDENTITY column, as with any referenced column, make sure it has the same datatype definition as the IDENTITY column.
• Refer to IDENTITY Columns with syb_identity
  Once you have defined an IDENTITY column, you need not remember the actual column name. You can use the syb_identity keyword, qualified by the table name where necessary, in a select, insert, update, or delete statement on the table.
• Automatically Create “hidden” IDENTITY Columns
  System administrators can use the auto identity database option to automatically include a 10-digit IDENTITY column in new tables.
• Using select into with IDENTITY Columns
  There are special rules for using the select into command with tables containing IDENTITY columns.