SQL Server 标识列-2005、2008 中 SCOPE_IDENTITY() 或 @@IDENTITY 返回错误值

作者:vkvi 来源:ITPOW(原创) 日期:2010-6-18

我们在 Microsoft SQL Server 2005、2008 中,使用 SCOPE_IDENTITY() 或 @@IDENTITY 时,可能会返回错误的值(特别是在多核 CPU 上)。

微软已经确认这是一个问题,并且目前无修复包。受影响的 SQL Server 包括:

  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2008 Developer
  • Microsoft SQL Server 2008 Enterprise
  • Microsoft SQL Server 2008 R2 Developer
  • Microsoft SQL Server 2008 R2 Enterprise
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Evaluation Edition
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2008 Standard
  • Microsoft SQL Server 2008 Workgroup
  • Microsoft SQL Server 2008 Web
  • Microsoft SQL Server 2008 Standard Edition for Small Business
  • Microsoft SQL Server 2008 R2 Standard
  • Microsoft SQL Server 2008 R2 Web
  • Microsoft SQL Server 2005 Workgroup Edition
  • Microsoft SQL Server 2005 Enterprise X64 Edition

我的解决办法

这是在微软的 Connect 上看到的,原理不详,但我试了它确实有效。

SET NOCOUNT ON SELECT SCOPE_IDENTITY() as lastIndex

也就是多加一个 SET NOCOUNT ON,ON、OFF 都可以阻止这个 BUG。(加了 SET NOCOUNT ON 并不会导致在 SQL Server 2000 中时出错。)

微软的解决方法

微软的几个解决方法我看得都头大,而且我试了其中一部分,竟然没用,不知道是不是我用错了。

微软建议目前不要使用这两个函数,而使用 OUTPUT 子句代替:

DECLARE @MyNewIdentityValues table(myidvalues int)
declare @A table (ID int primary key)
insert into @A values (1)
declare @B table (ID int primary key identity(1,1), B int not null)
insert into @B values (1)
select
    [RowCount] = @@RowCount,
    [@@IDENTITY] = @@IDENTITY,
    [SCOPE_IDENTITY] = SCOPE_IDENTITY()
 
set statistics profile on
insert into _ddr_T
output inserted.ID into @MyNewIdentityValues
    select
            b.ID
        from @A a
            left join @B b on b.ID = 1
            left join @B b2 on b2.B = -1
            left join _ddr_T t on t.T = -1
        where not exists (select * from _ddr_T t2 where t2.ID = -1)
set statistics profile off
 
select
    [RowCount] = @@RowCount,
    [@@IDENTITY] = @@IDENTITY,
    [SCOPE_IDENTITY] = SCOPE_IDENTITY(),
    [IDENT_CURRENT] = IDENT_CURRENT('_ddr_T')
select * from @MyNewIdentityValues
go

如果你非要使用 SCOPE_IDENTITY() 或 @@IDENTITY,请使用下面的解决方法:

法一、要查询中包含以下语句:

OPTION (MAXDOP 1)

注意:这会损伤 SELECT 性能。

法二、先读 SELECT 部分的值到变量集,再用 MAXDOP=1 插入到目标表,插入就不会并行了,性能就不受影响了。晕。

法三、执行下面的代码,将最大并行数改为 1。

sp_configure 'max degree of parallelism', 1
go
reconfigure with override
go

注意:这个对整个数据库性能操作比较严重,慎用。

相关文章