Set the table-specific identity gap when you create a table using either create table or select into.
This statement creates a table named mytable with an identity column:
create table mytable (IdNum numeric(12,0) identity) with identity_gap = 10
The identity gap is set to 10, which means ID numbers are allocated in memory in blocks of ten. If the server fails or is shut down with no wait, the maximum gap between the last ID number assigned to a row and the next ID number assigned to a row is ten numbers.
If you are creating a table in a select into statement from a table that has a specific identity gap setting, the new table does not inherit the identity gap setting from the parent table. Instead, the new table uses the identity burning set factor setting. To give the new table a specific identity_gap setting, specify the identity gap in the select into statement. You can give the new table an identity gap that is the same as or different from the parent table.
For example, to create a new table (newtable) from the existing table (mytable) with an identity gap:
select IdNum into newtable with identity_gap = 20 from mytable
