SQL Server 2012 autogrow column, value hopping problem

Introduced

Starting with the SQL Server 2012 version, when the SQL Server instance restarts, the values for the autogrow column of the table will jump, and the size of the specific hop value depends on the data type of the growth column. If the data type is integer (int), the jump value is 1000, and if the data type is a long integer (bigint), the jump value is 10000. From our project, this jumping problem is unacceptable, especially when displayed on the client side. This strange problem only exists in SQL Server 2012 and later versions, and this issue does not exist in previous versions of SQL Server 2012.

Background

A few days ago, our QA team colleague suggested that the value of the self-increment column of our table was a strange leap of 10000. That is, the last value of the self-increment column of our previous table is 2200, and now a new record is added, and the value of the self-increment column becomes 12200 directly. In our business logic such situations are not allowed to unfold on the client side, so we have to solve this problem.

Code use

At first we were all very strange, how did this happen? We typically do not manually insert any values into the self-increment column (it is possible to manually insert values to the self-increment column), and the value of the self-increment column is maintained by the database itself. A member of our core team began to study the problem and found the answer. Now, I'd like to elaborate on this and the solutions my colleagues have found.

How to reproduce this bug

You need to install SQL Server 2012 and then create a test database. Then create a table with the self-increment column:

CREATE TABLE mytesttable (Id int Identity (), Name varchar (255));

Now insert two data:

View results:

SELECT Id, Name from mytesttable;

At this point the result is the same as we expected. Now restart your SQL Server Service. There are several ways to restart the SQL service, which we can restart with SQL Server Manager:

After restarting, we insert 2 more data into the previous table:

Insert into mytesttable (name) VALUES (' Mr.tom2 '), insert into mytesttable (name) VALUES (' Mr.jackson2 ');

View results:

SELECT Id, Name from mytesttable;

Now that you see the result after restarting SQL Server 2012, the value of its self-increment column starts with 1002. That means jumping 1000. As I said before, if the data type of our self-increment is a long integer (bigint), it will have a jump value of 10000.

Is it really a bug?

Microsoft declares that this is a feature, not a bug, and is useful in many scenarios. But in our case, we don't need such a feature because the self-increment data is to be presented to the customer, and the customer will be surprised 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 presented to the customer, it may be acceptable. Therefore, this feature is usually only suitable for internal use.

Solution Solutions

If we are not interested in this "feature" provided by Microsoft, we can close it in two ways.

1. Using sequences (Sequence)

2. Registering the startup parameters for SQL Server-t272

Using sequences

First, we need to remove the self-increment column from the table. It then creates a sequence that does not have a cache function, inserting values based on this sequence. Here is the sample code:

CREATE SEQUENCE id_sequence as    INT    START with 1    INCREMENT by 1    MINVALUE 0    NO MAXVALUE   

Registering Startup Parameters-t272

Open SQL Server Configuration Manager. Select the SQL Server 2012 instance, right-click, and select the Properties menu. Locate the startup parameter in the pop-up window, and then register-t272. After you have completed the restart of SQL Server (SQLSERVER2012), perform the bug re-operation to verify that the issue is resolved.

Additional Instructions

If there are many self-contained tables in your database, and these tables have numeric jump problems, then a 2nd scenario is better. Because it is very simple, and the scope is server-level. The adoption of a 2nd solution will affect all databases on this service instance.

This article is a translation (English level is limited, hope understanding), the original link: SQL Server (s) Auto Identity Column Value Jump Issue

SQL Server 2012 autogrow column, value hopping problem