Question about skipping IDs in an identity column in MSSQL

Question

Say I have an MSSQL table with two columns: an int ID column that's the identity column and some other datetime or whatever column. Say the table has 10 records with IDs 1-10. Now I delete the record with ID = 5.

Are there any scenarios where another record will "fill-in" that missing ID? I.e. when would a record be inserted and given an ID of 5?

Answer 1

No, unless you specifically enable identity inserts (typically done when copying tables with identity columns) and insert a row manually with the id of 5. SQLServer keeps track of the last identity inserted into each table with identity columns and increments the last inserted value to obtain the next value on insert.

Answer 2

Only if you manually turn off identity IDs by using SET IDENTITY_INSERT command and then do a insert with ID=5

Otherwise MS-SQL will always increment to a higher number and missing slots are never re-used.