转：SqlServer2012⾃增列值突然增⼤1000的原因及解决⽅法
⾸先排除数据回滚及增加删除等操作。
按照这篇⽂章的⽅法，就可以解决问题了。
以下是内容：
Introduction
From SQL Server 2012 version, when SQL Server instance is restarted, then table's Identity value is jumped and the actual jumped value depends on identity column data type. If it is integer (int) data type, then jump value is 1000 and if big integer (bigint), then jump value is 10000. From our application point of view, this increment is not acceptable for all the business cases specially when the value shows to the client. This is the special case/issue ships with only SQL Server 2012 and older versions have no such issue.
从SQL Server2012版本中，当SQL Server实例重新启动，则表的IDENTITY值跳起来，实际跳下值取决于标识列的数据类型。如果它是整数（INT）数据类型，然后跳到值是1000，如果⼤的整数（BIGINT），然后跳值为10000。从我们的应⽤程序的⾓度来看，这个增量在所有的商业案例中是⽆法接受的，特别是当
值展⽰给客户端时。这是特殊情况，问题仅随SQL Server 2012，旧版本有没有这样的问题。
Background
A few days ago, our QA Engineer claims that one of our table's identity column jumped 10000. That means the last identity value of that table was 2200 now it is 12001. In our business logic is like that the value shows to the client and it will not be accepted by the client. So we must solve the issue.
Using the Code
The first time, we all are surprised and confused as to how it is possible? We usually do not insert any value in identity column (insert value to identity column is possible). The identity value is maintained by SQL Server itself. One of our core team members started investigation the issue and found out the solution. Now, I want to elaborate the issue and solution that was found out by my colleague.
How to Reproduce That?
You need to setup SQL Server 2012 and create a test database. Then create a table with auto identity column:
create table MyTestTable(Id int Identity(1,1), Name varchar(255));
Now insert 2 rows there:
insert into MyTestTable(Name) values ('Mr.Tom');
insert into MyTestTable(Name) values ('Mr.Jackson');
You see the result:
SELECT Id, Name FROM MyTestTable;
The result is as expected. Now just restart your SQL Server service. There are various ways in which you can do it. We did it from SQL Server management studio.
Now, insert another 2 rows to the same table again:
insert into MyTestTable(Name) values ('Mr.Tom2');
insert into MyTestTable(Name) values ('Mr.Jackson2');
Now see the result:
SELECT Id, Name FROM MyTestTable;
Now you see that after restarting the SQL Server 2012 instance, then identity value starts with 1002. It means it jumped 1000. Previously, I said that we also see if the data type of that identity column is bigint, then it will jump 10000.
Is it really a bug?
Microsoft declares it is a feature rather than a bug and in many scenarios it would be helpful. But in our case, it would not be acceptable because that number is shown to the client and the client will be surprised to see that new number after jump and the new number depends on how many times SQL Server is restarted. If it is not visible to the client, then it might be acceptable so that the number is used internally.
Solutions  解决⽅法sqlserver2012数据库还原
If we are not interested in this so called feature, then we can do two things to stop that jump.
Using Sequence  ⽅法⼀：使⽤序列
Register -t272 to SQL Server Startup Parameter  ⽅法⼆：注册-t272到SQL Server启动参数
Using Sequence
First, we need to remove Identity column from tables. Then create a sequence without cache feature and insert number from that sequence. The following is the code sample:
CREATE SEQUENCE Id_Sequence
AS INT
START WITH 1
INCREMENT BY 1
MINVALUE 0
NO MAXVALUE
NO CACHE
insert into MyTestTable values(NEXT VALUE FOR Id_Sequence, 'Mr.Tom');
insert into MyTestTable values(NEXT VALUE FOR Id_Sequence, 'Mr.Jackson');
Register -t272 to SQL Server Startup Parameter
Open SQLServer configuration manager from your server. Select SQL Server 2012 instance there right client and select Properties menu. You will find a tabbed dialog window. You select start up parameters tab from there and register -t272. Then restart SQL Server 2012 instance again and see the difference:
打开SQLServer configuration manager。左边选择服务。右边在对应实例右键选择属性。点击启动参数。把-t272添加进去。重启sqlserver 服务。再次新增数据进⾏观察。
Points of Interest
If too many tables contain identity column to your database and all contain existing values, then it is better to go for solution 2. Because it is a very simple solution and its scope is server wise. This means if you add SQL Server 2012 parameter -t272 there, then it will affect all your databases there. If you want to create a new database and you need auto generated number field, then you can use solution 1, that means use sequence value to a column instead of auto Identity value. There are so many articles you can find online about when you will use auto identity column when using sequence and advantages/disadvantages of each other. I hope you will read all those and take the appropriate decision.
如果您的表包含太多的标识列，并且所有的表都包含现有的值，那么最好是去⽤解决⽅案2。因为它是⼀个⾮常简单的解决⽅案，它的范围是服务器。这意味着如果你添加SQL Server 2012参数- t272，然后它会影响你所有的数据库⾥。
如果你想创建⼀个新的数据库，你需要⾃动⽣成的数字字段，那么你可以使⽤解决⽅案1，这意味着使⽤序列值的列，⽽不是⾃动识别值。
有这么多的⽂章，你可以到⽹上关于当你将使⽤⾃动识别列时，使⽤序列和优势/劣势的对⽅。我希望你会阅读所有这些，并采取适当的决定。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。