Adding IDENTITY Columns

Considerations for adding an IDENTITY column.

  • When adding a numeric or integer IDENTITY column to a table, make sure the column precision is large enough to accommodate the number of existing rows. If the number of rows exceeds 10 precision - 1, the SAP ASE server prints an error message and does not add the column.

  • When adding an IDENTITY column to a table, the SAP ASE server:
    • Locks the table until all the IDENTITY column values have been generated. If a table contains a large number of rows, this process may be time-consuming.

    • Assigns each existing row a unique, sequential IDENTITY column value, beginning with 1.

    • Logs each insert operation into the table. Use dump transaction to clear the database‚Äôs transaction log before adding an IDENTITY column to a table with a large number of rows.

  • Each time you insert a row into the table, the SAP ASE server generates an IDENTITY column value that is one higher than the value. This value takes precedence over any defaults declared for the column in the alter table statement or bound to it with sp_bindefault.