SQL increasing the ID by 1000 units

Question

SQL increasing the ID by 1000 units

Navigation

#1 by (8 votes)

6

I have a database in SQL Express with a reasonable amount of data for more than a year. It has always worked fine, but now it is presenting the following problem: sometimes the ID in some tables ends up increasing by 1000 units.

I did a little research on this on the internet and saw that this can happen when the service is restarted and the proposed solutions say to add the -t272 parameter on startup by the SQL settings manager. The only problem is that there in the manager the "SQL Server Services" section is empty.

I have already solved the problem by resetting the identity of the table to the last correct value. This works, but the problem ends up appearing again.

Is there any other solution to this problem other than the -t272 parameter? What really causes this?

sql sql-server

asked by anonymous 14.10.2014 / 19:45

1 answer

Function print with error Font size, px or pt?

score 8 · Accepted Answer

From Microsoft SQL Server version 2012 changed how to generate these values, when your instance of SQL Server is restarted, then the value of a Identity field

From our application point of view, this increment is not acceptable for all cases, especially when we use the value of the Id to display to the client.

It is a special case of SQL Server 2012 and older versions do not have this issue.

Solução 1:

Open SQL Server configuration manager , select the instance of SQL Server 2012 , right-click Properties Startup Parameters , enter the value -t272 and click Add and Apply .

Solução2:

RemovetheIdentityfieldsanduseSequence,wherewecansettheinitialvalueanditsincrement.

TheimagebelowshowsyouwhereyoucanviewtheSequencesalreadycreatedforthedatabaseandcreateanewoneaccordingtoyourneeds(formoredetailsonSequenceataglance here ):

Ifoundamoredetailedexplanationofthis here with examples.