Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

So I noticed a very strange thing when tinkering with my database.

I had just inserted a row which was ID 47, then after inserting some more, I realised that somehow my Identity had skipped all the way to 146, so it had skipped 999 to get there.

It's not a huge problem, or a gamestopper, just very strange, is there any way I can solve this? Short of backing up the data and recreating the table.

For the record, I didn't do anything at all between the data insertion - it seems to be totally random (of course it's not, something caused it I'm sure...)

Screenshot of strange data

share|improve this question
While this isn't exactly expected behavior, do you care enough about gaps that this needs to be "solved"? If you need to avoid gaps, as @gbn implied, you shouldn't be using IDENTITY because there is no way to prevent gaps. – Aaron Bertrand Apr 23 '13 at 12:52

This is a known behaviour difference in SQL Server 2012 because of a change in the way IDENTITY is implemented.

See related Connect Item for some discussion and possible workarounds (if this is a real issue for you)

share|improve this answer

IDENTITY sequences will never be contiguous

Row deletion is quite important if you have history/audit tables: you'd never want to reuse an ID that exists in some history/audit table somewhere of course

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.