How to avoid or Disable SQL Local DB ID Jump ?

Question

I have a C# application which uses SQL Local DB 2016. After Inserting some records into the Database the Primary ID which is set to Auto-Increment Jumps For. ex ID jumps to 1000 from 10 and keeps jumping like this. When I deploy this on Client Pc same happens. How do I disable this on Client PC ? What are the ways to disable this without installing any third party software on client pc?

Answer 1

The answer is that if you have a requirement to have contiguous ids, you should absolutely not use the IDENTITY features. It is designed to produce gaps. More precisely, it is designed to permit multiple processes inserting auto-numbered rows in parallel without having to wait to get the next id. This means that if one process is rolled back, the IDENTITY value that was assigned to it wil not be reused and there will be a gap. The reason you get this big jumps is that the IDENTITY value is only updated on disk only after each 1000 rows (depending on the data type). Again, this is for performance reasons.

Karen pointed you to a blog how you can change this. But please tell me, why would you press a go-slower button?

IF, and I said IF, you need contiguous numbers there are solutions. And, yes, such business requirements exists. I've worked with them myself. But it many cases, people just think it's for the sake of prettiness. And contiguous number will slow down parallel inserts. If you only want a surrogate keys, just accepts these gaps. The values does not mean anything anyway.

Answer 2

Rather than repeating the reason and fix, see the following post and note case 2 on how to resolve the issue.

Answer 3

Hi @YΛSH WΛKTΞ,

Agree with their response. This can occur if a large number of rows have been deleted or if the identity value has been reset manually. However, it is not recommended to disable or modify the identity seed increment value, as it may lead to data inconsistencies and conflicts.

SELECT IDENT_CURRENT('TableName') can help you check the current identity value.

https://learn.microsoft.com/en-us/sql/t-sql/functions/ident-current-transact-sql?view=sql-server-ver16

Best regards,

Seeya

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it.