SQL increasing ID by 1000 units

Question

I have a database in SQL Express with a reasonable amount of data for over a year. Everything always worked well, but now is presenting the following problem: certain times the ID in some tables ends up increasing of 1000 units.

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

I have solved the problem a few times by resetting the identity of the table pro last correct value. This works, but the problem ends up appearing again.

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

Answer 1

From SQL Server version 2012 to Microsoft changed the way to generate these values, when your SQL Server instance is restarted, then the value of a field Identity pula 1000 if it is integer field (int) and 10000 if the field is of type (bigint).

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

It’s a special case of SQL Server 2012 and older versions do not have that question.

Solução 1:

Open the SQL Server Configuration manager, select the instance of SQL Server 2012, right click on Properties/Startup Parameters, insert the value -t272 and click on Add and in Apply.

Solução 2:

Remove the fields Identity and use the resource Sequence, where we can define the initial value and its increment.

The image below shows where you can view the Sequences already created for the database and create a new one as your need (for more details on Sequence at a glance here):

I found a more detailed explanation (in English) about this here with examples.