Add IDENTITY Columns

You can add IDENTITY columns only with a default value of NOT NULL. You cannot specify a default clause for a new IDENTITY column.

To add an IDENTITY column to a table, specify the identity keyword in the alter table statement:

alter table table_name add column_name
     numeric(precision ,0) identity not null

The following example adds an IDENTITY column, record_id, to the stores table:

alter table stores 
    add record_id numeric(5,0) identity not null

When you add an IDENTITY column to a table, SAP ASE assigns a unique sequential value, beginning with the value 1, to each row. If the table contains a large number of rows, this process can be time consuming. If the number of rows exceeds the maximum value allowed for the column (in this case, 105 - 1, or 99,999), the alter table statement fails.

You can create IDENTITY columns with user-defined datatypes. The user-defined datatype must be a numeric type with a scale of 0.