Sql server skipping record id

Asked

Viewed 499 times

1

After changing an application record, the next record I entered in the table skipped 1000 increments in id. id’s

2
3
4
5
6
7
8
9
1015
1016
c# sql .net sql-server entity-framework
  • 1

    what you want is to understand or reset? better detail on the question..

  • 1

    another point, was any action taken in the bank that might have increased this? type performance tests??

  • Both of you, I want to know what’s going on and how to fix the situation, I’ve tried to erase the wrong records, but the increment continues from the biggest record I deleted.

  • I believed a method to change the record, after which the problem happened.

  • 1

    Only one seed of an Identity is used (I’m guessing the field is Identity, it seems kind of obvious but your question lacks a lot of information) when trying to insert a die. If there was an error in the Inserts, SQL already "used" that next value, probably what happened

  • The article "Generation of numerical sequences" deals with this subject. See https://portosql.wordpress.com/2019/04/21/geraca-sequentials-numeric/

Show 1 more comment

2 answers

4


Has an issue in SOEN that responds well to this problem, if using the SQL Server 2012 +:

This is normal behavior since Microsoft added sequences from _SQL Server 2012, changing the way keys are generated (including indicates this link detailing the implementation of).

In the answer indicates how to use the "old form" of generation, but I advise you to keep the current, since it is more performative. In case you need a sequel drilled, the ideal is to create a field codigo (for example) and control it as counter.

  • While this link may answer the question, it is best to include the essential parts of the answer here and provide the link for reference. Replies per link only can be invalidated if the page with the link is changed. - Of Revision

  • 1

    @Joãomartins I believe the answer explains the why "jump in id" regardless of the link, which in my view would answer the question. No?!

  • 1

    Not in my opinion. The link you placed to Soen may help, but it does not indicate which of the answers should be focused on (since all the answers to the question have positive votes), it did not include in its reply an excerpt from the link and for those who do not read English it will be difficult to understand what their answer intends to solve. But that is my opinion.

4

Complementing the rLinhares response, this problem can happen in two scenarios:

(1) if you are using an IDENTITY field from version 2012 when the service is restarted SQL makes a RESEED of IDENTITY adding 1000. A lot of people think it’s a bug, but it’s not. (2) in the use of cached sequences. If the instance is restarted the number of items you had cached from Quence are lost.

To circumvent scenario 1 you can enable the -t272 parameter as it is in the link quoted in the previous answer, but I would not indicate the manual control of this field for performance issues.

If you have to manually control this, each time you perform an INSERT, you have to do a SELECT before it will bring "MAX + 1"

I hope I’ve helped

Browser other questions tagged c# sql .net sql-server entity-framework

You are not signed in. Login or sign up in order to post.