Manual insertions into the IDENTITY column, deletion of rows, the value of the identity grab size configuration parameter, and transaction rollbacks can create gaps in IDENTITY column values. These gaps are not affected by the setting of the identity burning set factor configuration parameter.
For example, assume that you have an IDENTITY column with these values:
select syb_identity from stores_cal
id_col
-------
1
2
3
4
5
(5 rows affected)
You can delete all rows for which the IDENTITY column falls between 2 and 4, leaving gaps in the column values:
delete stores_cal where syb_identity between 2 and 4
select syb_identity from stores_cal
id_col
------
1
5
(2 rows affected)
After setting identity_insert on for the table, the table owner, Database Owner, or System Administrator can manually insert any legal value greater than 5. For example, inserting a value of 55 would create a large gap in IDENTITY column values:
insert stores_cal (syb_identity, stor_id, stor_name) values (55, "5025", "Good Reads")
select syb_identity from stores_cal
id_col
-------
1
5
55
(3 rows affected)
If identity_insert is then set to off, Adaptive Server assigns an IDENTITY column value of 55 + 1, or 56, for the next insertion. If the transaction that contains the insert statement is rolled back, Adaptive Server discards the value 56 and uses a value of 57 for the next insertion.
