Unexpected gaps in IDENTITY column

This is a known and expected issue - the way IDENTITY columns are managed by SQL Server has changed in SQL Server 2012 (some background); by default it will cache 1000 values and if you restart SQL Server, reboot the server, fail over, etc. it will have to throw out those 1000 values, because it won't have a reliable way to know how many of them were actually issued. This is documented here. There is a trace flag that changes this behavior such that every IDENTITY assignment is logged*, preventing those specific gaps (but not gaps from rollbacks or deletes); however, it is important to note that this can be quite costly in terms of performance, so I'm not even going to mention the specific trace flag here.

* (Personally, I think this is a technical problem that could be solved differently, but since I don't write the engine, I can't change that.)

To be clear about how IDENTITY and SEQUENCE work:

• Neither is guaranteed to be unique (you need to enforce that at the table level, using a primary key or unique constraint)
• Neither is guaranteed to be gapless (any rollback or delete, for example, will produce a gap, this specific problem notwithstanding)

Uniqueness is easy to enforce. Avoiding gaps is not. You need to determine how important it is for you to avoid these gaps (in theory, you should not care about gaps at all, since IDENTITY/SEQUENCE values should be meaningless surrogate keys). If it is very important, then you should not be using either implementation, but rather roll your own serializable sequence generator (see some ideas here, here and here) - just note that it will kill concurrency.

Lots of background on this "problem":

• SQL Server 2012 column identity increment jumping from 6 to 1000+ on 7th entry
• identity increment is jumping in sql server database
• Identity column value suddenly jumps to 1001 in sql server