The identity grab size configuration parameter allows each Adaptive Server 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 Adaptive Server 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, Adaptive Server 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, Adaptive Server 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")
Adaptive Server 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. Adaptive Server 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 Adaptive Server, 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.
