Inserting Values into IDENTITY Columns

insert id_table values ()

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

  Server failures can create gaps in IDENTITY column values. The maximum size of the gap depends on the setting of the identity burning set factor configuration parameter. Gaps can also result from manual insertion of data into the IDENTITY column, deletion of rows, and transaction rollbacks.

• The table owner or user with insert permission on the table can explicitly insert a value into an IDENTITY column after setting identity_insert table_name on for the column’s base table. Only table owner of user with identity_insert on the table can set identity_insert table_name on for the table. A user can set identity_insert table_name on for one table at a time in a database. When identity_insert is on, each insert statement must include a column list and must specify an explicit value for the IDENTITY column.

  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. 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.

  To insert an explicit value into an IDENTITY column, the table owner, database owner, or system administrator must set identity_insert table_name on for the column’s base table, not for the view through which it is being inserted.

• 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). Once an IDENTITY column reaches this value, any additional insert statements return an error that aborts the current transaction.

  When this happens, use the create table statement to create a new table that is identical to the old one, but that has a larger precision for the IDENTITY column. Once you have created the new table, use either the insert statement or the bcp utility to copy the data from the old table to the new one.

• Use the @@identity global variable to retrieve the last value that you inserted into an IDENTITY column. If the last insert or select into statement affected a table with no IDENTITY column, @@identity returns the value 0.

• An IDENTITY column selected into a result table observes the following rules with regard to inheritance of the IDENTITY property:

  • If an IDENTITY column is selected more than once, it is defined as not null in the new table. It does not inherit the IDENTITY property.

  • If an IDENTITY column is selected as part of an expression, the resulting column does not inherit the IDENTITY property. It is created as null if any column in the expression allows nulls; otherwise, it is created as not null.

  • If the select statement contains a group by clause or aggregate function, the resulting column does not inherit the IDENTITY property. Columns that include an aggregate of the IDENTITY column are created null; others are created not null.

  • An IDENTITY column that is selected into a table with a union or join does not retain the IDENTITY property. If the table contains the union of the IDENTITY column and a null column, the new column is defined as null; otherwise, it is defined as not null.