Like this story...?

 
 
 

Amazingly, I discovered recently that "identity" columns in Microsoft SQL Server are NOT guaranteed to be sequential! In fact, they are not even guaranteed to be unique, unless the column is also defined as the table's primary key. It turns out that Microsoft changed the number generation algorithm in SQL Server in 2012. New numbers get allocated in chunks of 1000 and are generally both unique and sequential, but if the server is stopped or rebooted it is possible that up to 1000 numbers may be skipped when the system comes back up again. If you don't believe me about this, read this Microsoft article about identity columns in SQL Server. There are many other articles and discussions about this problem on the various technical forums (such as stackoverflow.com and sqlservercentral.com).

declare @code int
declare @seq  tinyint
select  @seq = 0
while @seq < 3
   begin try
      select @code = max(OrdCode) from Order
      select @code = isnull(@code,0) + 1
      insert into Order (OrdCode, ...)
         values (@code, ...)
      select @seq = 255
   end try
   begin catch
      set @seq = @seq + 1
   end catch
if @seq < 255
.. return error

Recently this happened twice with a client of mine, both times on a Monday morning after the SQL Server machine had been restarted. A user created a new customer and found that the newly-generated customer code was not the next number as she expected, but nearly 1000 more. Normally this would not matter, because primary keys should be "dumb" and are usually not exposed to users so it makes no difference if some values are skipped, but in this case the auto-generated values are used by the client's admin people and they didn't like gaps in the numbering sequence.

I made it work properly by removing the primary key's identity property and coding a simple loop in the "insert" SQL procedure to handle the case when two (or more) simultaneous users get the same new primary key value - I find the highest value, add 1 and attempt to insert the row. If it fails I try again. After 3 failed attempts I exit with an error message. There are other, more technical ways to solve the problem, but this simple technique works - the numbers are guaranteed to be sequential.

I am truly amazed that I used SQL Server's "identity" feature for so many years and didn't realize that it has this unfortunate side-effect ...