Solve the problem of SQL Server 2012 auto increment ID disconnection (ID suddenly increased by 1000)

Time:2020-7-16

In the past two years, there has always been a problem with the auto increment ID column of the database. At the beginning, the auto increment was normal, and the increments were 1, 2, 3, and then suddenly changed to 1004 and 1005. In this way, I always thought that there was a problem with the program. Later, I found the problem on the foreign website after consulting the data for many times.

Starting from the SQL Server 2012 version, when the SQL server instance is restarted, theIdentity The value will be skipped, and the actual jump value depends on the identity column data type. If integer(int)Data type, the jump value is1000 ; if it is a large integer(bigint), the jump value is10000。 From our application perspective, this increment is unacceptable for all business cases, especially when the value is displayed to the customer. This is a special case / issue that comes with SQL Server 2012 only and is not available in earlier versions.

 

That is to say, as long as the SQL server instance is restarted, 1000 or 10000 auto increment IDS will be disconnected. Although the discontinuity of ID generally has no effect, OCD often can’t bear to suddenly change from one or two digits to four or five digits. The solution is also simple

Register – t272 with SQL server startup parameters

Open SQL Server Configuration Manager from the server. Right click the SQL Server service providerSelect the Properties menu.You will find a tabbed dialog window.From there, select the startup parameters tab to start parameter input-t272, click AddThen restart the SQL Server 2012 instance again.