当@@IDENTITY不返回null时，SCOPE_IDENTITY如何返回null？

Question

当@@IDENTITY不返回null时，SCOPE_IDENTITY如何返回null？

sql-serversql-server-2005

26

执行插入语句后，我要么选择 SCOPE_IDENTITY 或者 @@IDENTITY。 SCOPE_IDENTITY 返回 null 但是 @@IDENTITY 不会。

我不明白这为什么会发生。

你能想到原因吗？

- pn.

3

您能提供更多细节吗？例如，显示调用SCOPE_IDENTITY（）的代码，并解释任何其他变量（例如，是否涉及触发器）。 - Aaron Bertrand

5个回答

18

KM说得非常到位：

@@IDENTITY会给你返回最后一个插入的自增长ID值，并不管它是在哪张表中被插入的（需要考虑触发器，例如用于审计表，甚至有一系列嵌套的触发器……）
SCOPE_IDENTITY()则会返回在当前语句所处范围内最后一个被插入表的自增长ID，也就是那些被当前语句实际引用的表的ID（而不是所有被触发器更新过的表的ID）

- marc_s

1

当使用并行处理时，SCOPE_IDENTITY()无法返回正确的结果：https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=328811 - KM.

1

这个回答如何回答这个问题？保持scope_identity在范围内的解决方案是什么？那才是他问题的真正第二个问题。 - PositiveGuy

1

@CoffeeAddict：SCOPE_IDENTITY()会给你当前作用域内最后一个IDENTITY - 可能没有，所以你会得到NULL。但是如果你在一个没有IDENTITY的表上进行插入，然后触发器被触发，可能会将审计数据插入到另一个表中（该表具有IDENTITY列），那么@@IDENTITY将返回该值（来自审计表）。这对你有意义吗？所以请撤销那个踩踏 - 它完全没有必要... - marc_s

我遇到了类似的问题。据我所知，插入语句是唯一在范围内的语句。我会检查一下这个表上是否有触发器。（如果一个插入一个没有标识的表的触发器将范围标识设置为 null，那么 @@identity 也会为 null 吗？） - Mark Ainsworth

我解决了我的问题。这似乎与我的应用程序中打开的多个连接有关。我修改了应用程序，使所有更新使用相同的连接，问题就消失了。 - Mark Ainsworth

显示剩余2条评论

11

当使用sp_executesql进行插入时，SCOPE_IDENTITY将返回NULL，因为你已经不在插入的作用域内了！

- JulesB

1

当您通过.NET的Entity Framework 6 Context.Database.ExecuteSqlCommand("INSERT ...")进行插入时，它会使用sp_executesql包装命令，因此丢失了scope_identity。 - Marcos Dimitrio

3

我在MSDN上找到了这个:

如果在范围内尚未发生对标识列的任何INSERT语句调用函数，则SCOPE_IDENTITY()函数将返回null值。

您可以在此阅读：http://msdn.microsoft.com/en-us/library/ms190315.aspx

您的SQL代码将非常有帮助。

- Dustin Laine

0

在我的情况下，由于 INSTEAD OF INSERT 触发器的原因，SCOPE_IDENTITY() 和 OUTPUT INTO 都无法使用。解决方案是重新设计触发器使其成为 AFTER INSERT - 幸运的是，在我的情况下我能够这样做。

- Mr. TA

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- KM. · Accepted Answer

下面是一个使用 SCOPE_IDENTITY() 返回 null 但 @@IDENTITY 返回值的示例：

插入数据到一个没有标识列的表中，这个表有一个插入触发器，触发器会在一个带标识列的历史记录表中插入一条记录。在本地作用域中，SCOPE_IDENTITY() 将返回 null（因为没有标识列），但是 @@IDENTITY 将报告触发器中的标识列值。

请注意，SCOPE_IDENTITY() 存在已知的 bug：https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=328811

最好的解决方案是使用 OUTPUT INTO 来处理标识列，它可以捕获一组 ID 并且不受 SCOPE_IDENTITY() Bug 的影响:

declare @x table (tableID int identity not null primary key, datavalue varchar(10))
declare @y table (tableID int, datavalue varchar(10))

INSERT INTO @x values ('aaaa')
INSERT INTO @x values ('bbbb')
INSERT INTO @x values ('cccc')
INSERT INTO @x values ('dddd')
INSERT INTO @x values ('eeee')


INSERT INTO @x
    (datavalue)
    OUTPUT INSERTED.tableID, INSERTED.datavalue     --<<<<OUTPUT INTO SYNTAX
    INTO @y                                         --<<<<OUTPUT INTO SYNTAX
SELECT
    'value='+CONVERT(varchar(5),dt.NewValue)
    FROM (SELECT id as NewValue from sysobjects where id<20) dt
    ORDER BY dt.NewValue


select * from @x
select * from @y