-3

In our production server (SQL Server 2017) my user reported a large gap of the sequential number (IDENTITY column) used to reference claims. The sequential was 4251290.

After researching this known issue it should have a gap of 1,000 due to the cache.

A time difference of 3 minutes occurred until the next sequential number was generated and it was 4261204, afterwards all records were inserted as expected.

What could cause such a gap?

Improve this question

2 Answers 2

Reset to default
4

the sequential number (IDENTITY column) used to reference claims

This is a common misconception about IDENTITY columns. They are not intended to be a sequential number column per se. Groups of 1,000 or 10,000 identity values can be generated at once and cached based on data type, server version, and server settings. This cache can be lost / skipped / discarded in various circumstances (server restart, AG or cluster failover).

For your specific situation, it sounds like 10,000 values were cached, 93 got used, and then the cache was lost, resulting in the gap of 9,907.

You can disable this caching mechanism at the database level by setting the database scoped configuration option IDENTITY_CACHE to OFF.

However, as already mentioned in another answer, IDENTITY values generated by a T-SQL statement that is then rolled back are also lost. So just disabling the cache won't solve the problem of gaps completely.

If your application requires truly sequential numbers without gaps, you should consider using a SEQUENCE and coding with that goal in mind.

By the way, a similar misconception around IDENTITY values is that they are unique - this is not true. They can be reset and thus re-generate existing values. If that claim reference needs to be unique, ensure that there is a unique constraint / index / primary key defined on the column.

Improve this answer
1

What could cause an unexpected gap...in an IDENTITY column?

Rolled back transactions. Identity values don't get rolled back. Also, DELETEs against a table obviously don't reduce the current identity value. You can reset the seed with DBCC CHECKIDENT ('YourTableName', RESEED, 1).

For further information on what can cause gaps in an identity column, please see this StackOverflow Post.

Improve this answer
2
  • should I still reset the seed even though the problem normalized with the next batch of sequence numbers ?
    – George
    Commented Dec 29, 2022 at 19:05
  • @George There's probably no reason to. It's not unusual to have gaps in identity columns and you'll run into collisions when it catches back up to the values already used. No harm no foul in leaving it alone.
    – J.D.
    Commented Dec 29, 2022 at 19:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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