Hi
We have encountered a problem where PK int ID of an entity suddenly moves +1000 steps. Example: 1 2 3 1003 1004 2004 2005
This seems to be an issue related to the way Sql Server handles Identity column on a restart/reload: Consecutive values after server restart or other failures - SQL Server might cache identity values for performance reasons and some of the assigned values can be lost during a database failure or server restart. This can result in gaps in the identity value upon insert. If gaps are not acceptable then the application should use its own mechanism to generate key values. Using a sequence generator with the NOCACHE option can limit the gaps to transactions that are never committed. https://docs.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql-identity-property?view=sql-server-2017
Normally, this is not an issue. Our customer, however, needs a sequence that moves along in a more linear matter.
How would you do it? T-Sql Sequence, EF Sequence (or other magic) or any other solution (the ABP/Zero way)?
