I was just doing some test on my SQL Server 2012 to practice backup and restores.
I created a simple table with few column, one was
int) marked as an
I added 3 records, the
ID was automatically set to 1 then 2 then 3... ok
I back up the database, then did a full restore.
After the restore I tried adding a new record, but strange, the new ID was 1003 (instead of 4) and adding a new record again the ID was 1004 (so ok, it's again increasing from the previous one).
So I tried again to backup the database, and then restore it.
Tried to add a new record and guess... the new ID was 2004 (instead of 1005) and then the record after 2005, 2006 and so on.
It seem that after every restore the identity column is somehow messed up.
Is this a normal behavior?
Yes, this is normal behavior.
IDENTITY will reserve a certain size chunk of values and cache those. If your server goes down, or you do a backup/restore, the cached values that haven't been used yet from that reserved chunk are lost and won't come back.
And this is NOT a problem! The
IDENTITY system just makes sure it gives you properly, ascending values - it never guaranteed "no gaps" - and gaps are NOT a problem, really!
See Aaron Bertrand's blog post Bad Habits To Kick: Making Assumptions about IDENTITY for even more insights into
IDENTITY and what to except (and what not to expect) from