Using identity column gaps in SQL Server 2017

Question

I have a few 1000 jumps in my table. I've figured out the reason, rather late I would say, which is frequent server failure and restarts and executed set identity cache=off.

Hopefully, these large jumps will not occur. Now I want to reuse these numbers in the gaps for the new entries, what is the best way to do this? is changing the seed value is possible? Please note that I can not alter any existing data. Also, note the rate at which new entries are added is slow (less than 10 entries daily) and I can keep an eye on this database and change the seed value again manually when necessary.

Thank you very much.

Answer 1

You can write a script for each instance using SET IDENTITY INSERT table_name ON and SET IDENTITY INSERT table_name OFF at the start and end of your script. The full documentation is here. You can only use it on one table at a time.

Changing the seed will have no effect as the next highest value will always be used.

The following script will help with identifying gaps.

SELECT  TOP 1
    id + 1
FROM    mytable mo
WHERE   NOT EXISTS
    (
    SELECT  NULL
    FROM    mytable mi 
    WHERE   mi.id = mo.id + 1
    )
ORDER BY
    id

Which is from the this question/answer

UPDATE

A possible strategy would be to take the database offline, use SET IDENTITY INSERT to fill the gaps/jumps with the required ID but otherwise minimum/empty data and then make live again. Then use the empty records until all are used and then revert to the previous method.