Parameters for Controlling Identity Gaps

identity_gap

Table-specific

create table or select into

Creates ID number blocks of a specific size for a specific table. Overrides identity burning set factor for the table. Works with identity grab size.

identity burning set factor

Server-wide

sp_configure

Indicates a percentage of the total available ID numbers to allocate for each block. Works with identity grab size. If the identity_gap for a table is set to 1 or higher, identity burning set factor has no effect on that table. The burning set factor is used for all tables for which identity_gap is set to 0.

When you set identity burning set factor, express the number in decimal form, and then multiply it by 10,000,000 (10⁷ ) to get the correct value to use with sp_configure. For example, to release 15 percent (.15) of the potential IDENTITY column values at one time, specify a value of .15 times 10⁷ (or 1,500,000):

sp_configure "identity burning set factor", 1500000

identity grab size

Server-wide

sp_configure

Reserves a block of contiguous ID numbers for each process. Works with identity burning set factor and identity_gap.