identity increment bug

Question

I set for a table in main field, identity increment =27
for my "id" field (int , NOT NULL, PRIMARY KEY)

but sudenly it jump about 26943 instead 27. why?
i athach a image to explan

17403
17430
17457
17484
17511
17538
17565
17592
44538
44565
44592
44619

thanks for your time

Answer 1

I think that such gap is not a defect and is described in documentation: "SQL Server might cache identity values for performance reasons [...]. This can result in gaps in the identity value upon insert" (https://learn.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql-identity-property). Gaps also appear in case of failures or rolled back transactions.

If minimising the gaps is crucial, consider the sequences (CREATE SEQUENCE ... NO CACHE, etc.).

Answer 2

Thanks for your advise

anyway I think it is a HUGE gap!

Answer 3

It is completely normal and expected for identity fields to have gaps as Viorel-1 said.

The primary reason for such a big gap is almost always a transaction which was rolled back. The identity numbers used by that transaction are not recovered when the transaction is rolled back.

Answer 4

The current IDENTITY value is stored somewhere in the database. It would not be efficient to update that value on disk every time a row inserted. You should keep in mind that IDENTITY is a feature to permit highly concurrent inserts. For this reason, SQL Server updates the value only on every 1000th insert. This means that if SQL Server unexpectedly crashes, you can "lose" numbers.

As Tom says, you can also lose values because of a rollback. This is to permit parallel process to insert without having to wait for each other.

If you actually want something without gaps, you should not use IDENTITY. It is designed to permit gaps.

Answer 5

Hi @Omid Omid

identity increment bug

Actually, it is not a bug. This is by design in SQL server. SQL Server guarantees that the next number generated will be unique and incremental, it does not guarantee it will be in sequence.

suddenly it jumps about 26943 instead 27. why?

There might be couple of potential causes:

1. Rows were deleted.
2. Identifiers were allocated in a transaction which wasn't committed.
3. The database engine allocated potential identifiers as an internal performance tuning and that allocation was lost (unexpected server re-start, for example).
   Besides, there could be more potential causes I'm not thinking of.

If you need a gapless column for query, then you could use ROW_NUMBER along with some calculation, like this:

SELECT ROW_NUMBER()OVER(ORDER BY id) * 27 + 17403 AS Gapless_id  
FROM Your_Table  

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.