-1

I have a huge problem in finishing my windows form application, I am using the latest Microsoft SQL Server Management Studio 18 and i am having a problem with auto-increment columns, after restarting the SQL Server values are jumping from 1 to 1000+ which is changing everything in the application because i have some queries based on the transaction number that is being jumped

https://i.stack.imgur.com/t1Be7.jpg

Please do not remove my post and say that it is a duplicated question, it is not the same case as in SQL Server 2017 or older, each version has it's own way to turn off IDENTITY_CACHE, in the version that I am using there is nothing called IDENTITY_CACHE, I couldn't find a fix on google, Thank you.

Michał Turczyn
  • 32,028
  • 14
  • 47
  • 69
Roy Hayek
  • 43
  • 2
  • 4
  • 1
    [Identity increment is jumping in SQL Server database](https://stackoverflow.com/a/26186782/5070879) - traceflag Anyway depending on continuous values of IDENTITY is dangerous. Gaps are always possible - simple scenario with transaction that was rollbacked – Lukasz Szozda Aug 31 '19 at 08:26
  • The problem is that there is no SQL Server Configuration Manager in the newest version, i would refer to that link if it was available – Roy Hayek Aug 31 '19 at 08:31
  • Which version of SQL Server are you using exactly? Are you using Azure? – Lukasz Szozda Aug 31 '19 at 08:32
  • https://imgur.com/ecU4IRK – Roy Hayek Aug 31 '19 at 08:37
  • SSMS is a client tool. It is not related with SQL Server version you are using. You should connect via RDP to your instance and simply use Configuarion Manager to set trace flag if you are not using SQL Server 2017 or above – Lukasz Szozda Aug 31 '19 at 08:38
  • Is there a tutorial or steps on how to do it? – Roy Hayek Aug 31 '19 at 08:43
  • @RoyHayek i think SSMS just does that im pretty sure it wont affect how your software actually works or anything else. – mihailovsMark Aug 31 '19 at 10:10
  • SQL Server is multi-threaded and the order that data is stored and retrieve is RANDOM. So you have an ORDER BY in your query to get data sequentially. Also the data posted does not indicate an issue. Data could of been delete in the database an could account for the missing numbers. – jdweng Aug 31 '19 at 12:49

2 Answers2

1

You have a huge problem because you seem to be assuming that identity() columns do not have gaps.

They are simply guaranteed to be non-decreasing. And -- if also declared unique -- they are declared to never be duplicated. That's it. Gaps are allowed.

In your case, you are probably seeing that SQL Server "reserved" a bunch of numbers that weren't used. When you stopped and restarted the server, it "forgot" they weren't used.

There are other reasons for gaps, notably:

  • Transaction failures on inserts.
  • Deletions.

But for this size gap, I am guessing it is the "reservation" issue.

There is a good reason for this behavior: performance. You can implement logic (say using sequences) and triggers. This requires significantly more overhead for locking tables during updates.

In the end you should not be caring what the transaction number actually is. That is, you should not be making such assumptions about the primary key column.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

It because IDENTITY works int hat way it doesn't repeat deleted values, i.e. once you create row, that column will increase by 1, giving for example 14. When you delete that row and latest ID would be 13, next inserted row will be 15, not 14. This is why you are getting gaps.

Most probably you have deleted somwhere rows between 4 and 2002, that's why you're getting such results.

You can use RESEED function in SQL Server for the identity to start from 4 again.

Michał Turczyn
  • 32,028
  • 14
  • 47
  • 69