Reserve a Block of IDENTITY Column Values

The identity grab size configuration parameter allows each SAP ASE process to reserve a block of IDENTITY column values for inserts into tables that have an IDENTITY column.

This configuration parameter reduces the number of times an SAP ASE engine must hold an internal synchronization structure when inserting implicit identity values. For example, to set the number of reserved values to 20:

sp_configure "identity grab size", 20

When a user performs an insert into a table containing an IDENTITY column, SAP ASE reserves a block of 20 IDENTITY column values for that user. Therefore, during the current session, the next 20 rows the user inserts into the table have sequential IDENTITY column values. If a second user inserts rows into the same table while the first user is performing inserts, SAP ASE reserves the next block of 20 IDENTITY column values for the second user.

For example, suppose the following table containing an IDENTITY column has been created, and the identity grab size is set to 10:

create table my_titles
(title_id   numeric(5,0)    identity,
title          varchar(30)     not null)

User 1 inserts these rows into the my_titles table:

insert my_titles (title)
values ("The Trauma of the Inner Child")insert my_titles (title)

values ("A Farewell to Angst")
insert my_titles (title)
values ("Life Without Anger")

SAP ASE allows user 1 a block of 10 sequential IDENTITY values, for example, title_id numbers 1–10.

While user 1 is inserting rows to my_titles, user 2 begins inserting rows into my_titles. SAP ASE grants user 2 the next available block of reserved IDENTITY values, that is, values 11–20.

If user 1 enters only three titles and then logs off SAP ASE, the remaining seven reserved IDENTITY values are lost. The result is a gap in the table’s IDENTITY values. To avoid large gaps in the IDENTITY column, do not set the identity grab size too high.