0

I am using SQL Server 2019 with Asp.Net Core 3.1.

I reset the data increment to start from 1.

Data is being entered, and the number is increasing. But sometimes it goes from ID = 56 to Id = 154, or from ID = 126 to ID = 1124.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Sras
  • 1,686
  • 2
  • 19
  • 29
  • check your code, you do it wrong, also do you have read that similar answer -> https://stackoverflow.com/questions/47791479/sql-server-primary-key-id-does-not-increment-correctly – Aristos Oct 15 '20 at 08:09
  • 2
    So you get gaps in your id's, why do you care? – HoneyBadger Oct 15 '20 at 08:10
  • 1
    maybe this article is the answer ? https://www.databasejournal.com/features/mssql/how-to-eliminate-gaps-in-identity-values-in-sql-server-by-disabling-the-identity-cache.html – Aristos Oct 15 '20 at 08:14
  • @HoneyBadger, Just to make sure it is nothing went wrong in the future – Sras Oct 15 '20 at 08:16
  • any possible way to ensure it is increasing properly. – Sras Oct 15 '20 at 08:21
  • 2
    It ***IS*** increasing properly - the **only** guarantee you get from SQL Server is that the values are **increasing** - and they are. There are no other guarantees given. – marc_s Oct 15 '20 at 08:24
  • I use this "ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE=OFF;" hope this help – Sras Oct 15 '20 at 08:31
  • `IDENTITY` is working exactly as it's meant to be. Nothing in `IDENTITY` states the values will be sequential and the process is, and gaps occurring is by design. If you want sequential values `IDENTITY` is not the correct choice. turning `IDENTITY_CACHE` off can help, however, it does come with performance considerations. – Thom A Oct 15 '20 at 08:39
  • Let me just put this here... [SQL Server’s identity column misconceptions](https://zoharpeled.wordpress.com/2019/10/06/sql-servers-identity-column-misconceptions/) – Zohar Peled Oct 15 '20 at 08:44

0 Answers0