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, Adaptive Server 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.
