SQL Server: Identity column is taking random range

Question

While working on the SQL Server database, I found that identity column value suddenly jumps to 101 in SQL server 2012 Enterprise edition.

There were total 10 records into database with the IDs (1 to 10). There were no delete operation was performed and while inserting the record number 11 it tool 101.

And I found out the cause after browsing some online helps:

Here, Here and Here.

Is there any other cause behind happening this?

Answer 1

Is possible by design. Identities are not guaranteed to be contiguous nor monotonous. Nobody can help, because there isn't any help needed. Applications should never rely on identity not having gaps. Having identity value rows 1,2, 100, 1000 is always OK.

How is this possible? Well, to start with, consider the trivial case of DELETE. Obviously deleting rows would leave gaps behind.

A more subtle problem is when gaps in identities appear even though there are no deletes. This happens because there are deletes, you are just not aware of them. They are caused by uncommitted transactions rolling back. Rollbacks cannot be prevented, since banning rollbacks is akine to saying 'This will always succeed!', ie. ignoring reality (failures). A client disconnecting in a middle of an insert is a rollback and you can never prevent that.

Always expect gaps. Code expecting gaps. Never rely on identities being contiguous.

I would have to use sequence to solve this problem

Sequences have exactly the same problem. This is not some oversight on design, this is a fundamental issue: there is no performant way to generate contiguous, gap free IDs. The only solution is to serialize all inputs (eg. table X lock) and nobody wants to do that.