如何避免“SQL Server 自动使用新插入的值作为当前标识值”。

Question

我

按照 Microsoft http://msdn.microsoft 使用 SQL Server 2008。 com/en-us/library/ms188059.aspx

当我执行以下命令时，

set identity_insert on  
//insert statements here
set identity_insert off

列的标识设置为最大值。我可以避免这种情况吗？

考虑以下场景，

有 2 行

id, name  comm  
1,  John, 232.43  
2,  Alex, 353.52  

我的表现在使用上面的代码

10, Smith, 334.23

，当我按照上面的链接插入时，SQL Server 自动将标识设置为 10。因此，对于新插入的记录（不使用identity_insert on），id 会自动设置从11开始。

我希望身份值为3，在使用identity_insert on/off后

请帮忙。

Answer 1

这是用于此讨论的测试表

create table t4721736 ( id int identity primary key, name varchar(10), comm money )
insert t4721736 select 'John', 232.43 -- id=1
insert t4721736 select 'Alex', 353.52 -- id=2

-- check contents    
select * from t4721736 

-- do all this in a transaction
BEGIN TRAN

-- dummy insert
insert t4721736 select 'dummy', null

-- get what the id should be
declare @resetto bigint
set @resetto = scope_identity()

-- remove dummy record
delete t4721736 where id = @resetto

-- perform the insert(s)
set identity_insert t4721736 on;
insert t4721736(id,name,comm) select 10000000, 'Smith', 334.23;
set identity_insert t4721736 off;

-- reset the identity
set @resetto = @resetto - 1  -- it needs to be 1 prior
DBCC CHECKIDENT(t4721736, RESEED, @resetto)

COMMIT

假设您完全理解（我相信您这样做），一旦范围达到具有指定 ID 的记录，它就会失败。 SQL Server 不会对已附加记录的 ID 执行任何自动跳过。

这不会是一个问题，因为当我
使用identity_insert on，值插入
id 将大于 1000 万。
所以不会有任何问题
冲突

原因，请通过将上面代码中的“10000000”更改为“10”来缩短该过程。然后，跟进这些：

-- inspect contents, shows records 1,2,10
select * from t4721736

-- next, insert 7 more records, bringing the id up to 9
insert t4721736 select 'U3', 0
insert t4721736 select 'U4', 0
insert t4721736 select 'U5', 0
insert t4721736 select 'U6', 0
insert t4721736 select 'U7', 0
insert t4721736 select 'U8', 0
insert t4721736 select 'U9', 0

最后，尝试下面的下一个插入

insert t4721736 select 'U10', 0

Answer 2

您可以使用 DBCC CHECKIDENT：

DBCC CHECKIDENT ("MyTable", RESEED, 3);
GO

但是，您插入了一条记录 ID 10，所以是的，下一条确实是 11。

它记录在命令中：

如果表的当前标识值小于标识列中存储的最大标识值，则使用标识列中的最大值进行重置。

你不能两全其美。要么具有最低的 ID 作为基本种子的值，要么不具有。

Answer 3

如果您要插入的这些行是特殊/魔术行（因此它们需要特定的 ID），您是否考虑过使这些行具有负 ID 值？这样就不会发生冲突，并且 IDENTITY 值不会因添​​加它们而被重置。

如果出于其他原因您需要插入具有截然不同的 ID 值的这些行，也许您可​​以扩展您的问题以提供一些相关信息 - 我们也许能够提供更好的解决方案。

Answer 4

解决“植入错误”困境的另一种方法是创建您自己的身份生成器过程和跟踪表。该表包含表名和下一个 ID 应该是的值。这样您就可以随时将其重置为任何值。该过程将包括检查下一个生成的键是否存在的逻辑，如果存在，它将递增该键，直到找到表中不存在的 ID 并将其传回给您。必须在所有插入件上实施此操作才能正常工作。这可以通过触发器实现。缺点是处理开销比使用 Damien_The_Un believer 所建议的负数要多。