Manually inserting values into the IDENTITY column, deleting rows, setting the identity grab size value, and rolling back transactions can create gaps in IDENTITY column values. Setting the identity burning set factor does not affect these gaps.
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
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
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
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.
