Whenever my PC crashes, my SQL DB adds 1000 to the next ID

Question

I'm running a .NET stack from my desktop. I have Blazor and SQL Server in my dev environment, running the entire stack and database locally on this PC.

Lately the electricity on my block has been having issues. The company is working on fixing it. Power went out, and when I turned my computer back on, I noticed something about my application...

For every next entry in any table in my database the ID becomes 1004. I was thinking maybe because the crash for some reason it just adds 1000. But actually this happened a second time the next day, and at this time I had a different number of entries in every table, but it looks like the exact same thing happened. The next entries started at ID 1004.

I know this power issue is not ideal, and it should be solved soon. But if this happened to a client who purchased one of my apps, it would be bad for their data. I know I could use GUID, but I've heard as a high number of those entries stack it becomes a heavier resource load. How can I enforce the integrity of the ID, this is my primary key. What's the best way to handle this sort of issue?

I have investigated my application to make sure entries were not going through while this power outage occurred, but everything looks right. Those methods are usually super quick anyway, I do keep SSMS up and running most of the time. I wonder if this could be related?

Answer 1

In a database, sequences are designed to generate unique values, period.
... or almost period actually. It also needs to:

1. Do so as quickly as possible, using as little computing/memory/storage resource as possible.
2. Support concurrent operations from multiple clients.

Guaranteeing sequences have no gaps would break the above requirements.

What you are seeing is the consequence of the DBMS reserving sequence values in advance (that makes the number generation faster) but not remembering which values were actually used, only which values have been reserved, through the outage (that limits the resources needed to make it work).

Other events create gaps in a sequence:

• Deleting a record (e.g. deleting id 2 when the table has id 1 and 3).
  Rather than a gap in the sequence, it is a gap in table using that sequence for its primary key, but it makes no difference after the DELETE is committed.
  I invite you to imagine how much slower sequences would get if the DBMS first needed to check if an old value got freed.
• Using a value in a transaction that later gets rolled back.
  You may think "then why does the DBMS not put the value back to the pool for the next call to the sequence?" and this answer to it is the concurrent operations. It would also slow down the DBMS a lot if it had to check no other calls to the sequence were made before the rollback, especially considering that it would require threads to share information, which is never easy/fast to do in programming.

To repeat: the cost of [fast + minimum resources used + support of concurrent access] is that a sequence does not allow to guarantee the values are generated with no gaps; that very much includes outage.
This will most certainly not have any negative repercussion on the application you are developing, as for all the other applications that connect to a database worldwide.

Answer 2

SQL Server reserves 1000 numbers for the primary key that are auto incremented. If the database service is stopped normally, the unused number for keys will be released, but if there is an interruption in the work of the database engine, like you said, then the database engine so that there is no problem in the main key mechanism, the next time it is executed, it considers these 1000 codes as used and reserves 1001 to 2000 this time.

This is not a problem in itself, but it usually becomes a problem when you'd like to use the primary key as a sequence counter for purposes such as traditional archiving. For this purpose, you should consider a separate field that you manage yourself.

For more info you can refer to ^, ^