sa_reset_identity system procedure

Allows the next identity value to be set for a table. Use this to change the autoincrement value for the next row that will be inserted.

Syntax
sa_reset_identity( 
tbl_name, 
owner_name, 
new_identity 
)
Arguments
  • tbl_name   Use this CHAR(128) parameter to specify the table for which you want to reset the identity value. If the owner of the table is not specified, tbl_name must uniquely identify a table in the database.

  • owner_name   Use this CHAR(128) parameter to specify the owner of the table for which you want to reset the identity value.

  • new_identity   Use this BIGINT parameter to specify the value from which you want the auto-incrementing to start.

Remarks

The next identity value generated for a row inserted into the table is new_identity + 1.

No checking occurs to see whether new_identity + 1 conflicts with existing rows in the table. For example, if you specify new_identity as 100, the next row inserted gets an identity value of 101. However, if 101 already exists in the table, the row insertion fails.

If owner is not specified or is NULL, tbl_name must uniquely identify a table in the database.

Permissions

DBA authority required

Side effects

Causes a checkpoint to occur after the value has been updated

Example

The following statement resets the next identity value to 101:

CALL sa_reset_identity( 'Employees', 'DBA', 100 );