IDENTITY Columns

Information about using IDENTITY columns.

The first time you insert a row into the table, the SAP ASE server assigns the IDENTITY column a value of 1. Each new row gets a column value that is 1 higher than the last value. This value takes precedence over any defaults declared for the column in the create table statement or bound to the column with sp_bindefault.

The maximum value that can be inserted into an IDENTITY column is 10 ^precision - 1 for a numeric. For integer identities, it is the maximum permissible value of its type (such as 255 for tinyint, 32767 for smallint).

See System and User-Defined Datatypes in Reference Manual: Building Blocks for more information about identifiers.
Inserting a value into the IDENTITY column allows you to specify a seed value for the column or to restore a row that was deleted in error. The table owner, database owner, or system administrator can explicitly insert a value into an IDENTITY column after using set identity_insert table_name on for the base table. Unless you have created a unique index on the IDENTITY column, the SAP ASE server does not verify the uniqueness of the value. You can insert any positive integer.
You can reference an IDENTITY column using the syb_identity keyword, qualified by the table name where necessary, instead of using the actual column name.
System administrators can use the auto identity database option to automatically include a 10-digit IDENTITY column in new tables. To turn on this feature in a database, use:
```
sp_dboption database_name, "auto identity", "true"
```
Each time a user creates a table in the database without specifying a primary key, a unique constraint, or an IDENTITY column, the SAP ASE server automatically defines an IDENTITY column. This column, SYB_IDENTITY_COL, is not visible when you retrieve columns with the select * statement. You must explicitly include the column name in the select list.
Server failures can create gaps in IDENTITY column values. Gaps can also occur due to transaction rollbacks, the deletion of rows, or the manual insertion of data into the IDENTITY column. The maximum size of the gap depends on the setting of the identity burning set factor and identity grab size configuration parameters, or the identity_gap value given in the create table or select into statment. See Managing Identity Gaps in Tables in the Transact-SQL Users Guide.