Server SCOPE_IDENTITY() vs @@IDENTITY

Question

不使用SCOPE_IDENTITY()和切换到@@IDENTITY会有什么好处吗？对于我所讨论的区域，是安装脚本的一部分，该脚本为我们的客户建立了一个数据库。它在一个表中插入一个记录，并使用该表中的标识符键将其插入到另一个表中的外键中。我们要这样做两次。

我们似乎有一种罕见的情况，在第二次发生这种情况时，我们将从第一个插入到第二个表中的id插入到第二个表中，这将导致数据出现问题。有可能是其他原因造成的，但我的线索似乎把矛头对准了SCOPE_IDENTITY()，认为它可能是罪魁祸首。

Declare @TheId int

Insert into dbo.TableName (Name) Values ('xxxx')
Select @TheId = SCOPE_IDENTITY()

-- some code here that uses @TheId
-- ...

Insert into dbo.TableName (Name) Values ('yyyy')
Select @TheId = SCOPE_IDENTITY()

-- some code here that uses @TheId
-- at this point, we may have the condition that SCOPE_IDENTITY() still has the value before that 2nd insert...

Answer 1

scope_identity()在此上下文中具有先前id值的唯一方法是，如果INSERT语句不创建任何行。在这种情况下，@@IDENTITY不会修复任何事情。事实上，@@IDENTITY不那么具体，因此只能希望使事情变得更糟。

您可以做的是为第二个插入使用一个不同的变量。或者，可以在第二次插入运行之前将@TheId设置为NULL。这样，你就能判断出什么地方出了问题。@@rowcount在这方面也很有用。

我在评论中确实看到了这一点：

“第二次插入没有失败，因为在数据库中找到了记录。

我告诉您，在代码运行之前，数据库中的记录已经是了。此外，如果表上有限制，这可能是插入失败的原因。

Answer 2

在proc或脚本的范围内，第一个插入所创建的“种族隔离”与第二个插入所创建的“种族隔离”的目标不同。虽然可以重用变量，但对于代码块中的多个DML语句来说，这并不是一个很好的实践。在这个脚本中，我添加了TRY/CATCH并设置了XACT_ABORT，以确保块中所有DML语句的完整回滚。

就像这样

set nocount on;
set xact_abort on;

begin transaction
begin try
    Insert into dbo.TableName (Name) Values ('xxxx');
    if @@rowcount=1
        begin
            Declare @Id1 int = SCOPE_IDENTITY();

            -- some code here that uses @Id1
            -- ...
        end
    else
        throw 50000, 'The first insert failed', 1;

    Insert into dbo.TableName (Name) Values ('yyyy');
    if @@rowcount=1
        begin
            Declare @Id2 int = SCOPE_IDENTITY();

            -- some code here that uses @Id2
            -- ...
        end
    else
        throw 50000, 'The second insert failed', 1;

    commit transaction
end try
begin catch
    /* put error handling here */

    rollback transaction
end catch