Introduction
Starting with SQL Server 2012 releases, when the SQL Server instance is restarted, the value of the auto-grow column of the table jumps and the specific jump value is determined by the data type of the growth column. If the data type is int, the jump value is 1000, and if the data type is bigint, the jump value is 10000. From our project, this kind of jumping problem is unacceptable, especially when shown on the client side. This strange issue is only present in SQL Server 2012 and later, and it does not exist in versions prior to SQL Server 2012.
background
A few days ago, a colleague from our QA team suggested: The value of the self-incrementing column of our table jumped by 10,000 inexplicably. In other words, the last value of the self-incrementing column in our table was 2200, but now that we have added a new record, the value of the self-incremented column has become 12200. In our business logic, situations like this are not allowed on the client, so we need to solve this problem.
Code usage
At first we were all very strange, how did this happen? We usually don't manually insert any values into self-incrementing columns (manually inserting values into self-inflating columns is fine), and the values of self-inflating columns are maintained by the database itself. A member of our core team started researching this question and found the answer. Now, I would like to explain this problem in detail and the solution my colleague found.
How to reproduce this bug
You will need to install SQL Server 2012 and then create a test database. Then create a table with self-growing columns:
Now insert two pieces of data:
Review the results:
At this point, the result was the same as we expected. Now restart your SQL Server Service. There are several ways to restart SQL Service, and here we use the SQL Server Manager to restart it:
After restarting, we insert 2 more pieces of data into the table just now:
Review the results:
Now you see the results after restarting SQL Server 2012, and its self-incrementing column values start at 1002. That is, jumped 1000. As mentioned earlier, if the data type we add is bigint, its jump value will be 10,000.
Is it really a BUG?
Microsoft states that this is a feature, not a bug, and is useful in many scenarios. But in our case, we don't need such a function, because this self-incremental data is meant to be shown to customers, and customers will feel strange if they see such jumping data. And the jump value is determined by the number of times you restart SQL Server. If this data is not shown to customers, it may be acceptable. Therefore, this feature is usually only suitable for internal use.
solution
If we are not interested in this "feature" offered by Microsoft, there are two ways we can turn it off.
1. Using Sequences
2. Register the startup parameter -t272 for SQL Server
Use sequences
First, we need to remove the self-incrementing columns of the table. Then create a sequence without caching, from which the numeric values are inserted. Here's the sample code:
Register the startup parameter -t272
Open SQL Server Configuration Manager. Select your SQL Server 2012 instance, right-click, and select the Properties menu. Find the launch parameters in the pop-up window and register -t272. After completion, restart SQL Server (SQLSERVER2012) in the figure below, and then perform the bug reproduction operation to verify whether the problem has been resolved.
Additional Notes:
If you have a lot of self-inflating tables in your database and they all have numeric jump issues, then it's better to use the second option. Because it's very simple and the scope is server-level. Adopting the second solution will affect all databases on this service instance.
|
Posted on 8/23/2018 12:03:06 PM
|
|
|
|
Landlord|
Posted on 7/8/2020 3:15:52 PM
|
