I have created an application, what is connected to MS SQL 2012 databases. We have more then 60 clients in the local area. It works well, but today I have found a little issue. When I have checked the datas, I have found missing IDs.
The ID is not depends on the app, it is SQL setting. When somebody save new data (I am using "INSERT" command), then the sql table increment the ID by 1. But here are missing the 19571 and 19572.
I am just wondering, how it is possible.
It would be possible through any insertion which was in a transaction which rolled back, the number would of been allocated and used by the transaction, but on rollback, you would find it missing.
Normally with identity fields you should not be worried about gaps in the IDs, they should be considered arbitrary unique identifiers.
Presumably, you are referring to an identity column. Basically, SQL Server does not guarantee gapless numbers. The only guarantee is that these are increasing -- and unique if the column is declared unique or a primary key.
There are two primary reasons why this occurs.
This is actually explained in the documentation. In particular, the following are not guaranteed
- Uniqueness of the value
- Consecutive values within a transaction
- Consecutive values after server restart or other failure. – SQL Server might cache identity values for performance reasons.
- Reuse of values.