Scope_Identity()、Identity()、@@Identity和Ident_Current()有什么区别？

Question

我知道Scope_Identity()、Identity()、@@Identity和Ident_Current()都可以获得标识列的值，但我想知道它们之间的区别。

争议的一部分是这些函数中的“作用域”是什么意思？

我也希望能有一个简单的例子来展示它们在不同情况下的使用方式。

- Orson

---

不要忘记 SQL Server 中与 SCOPE_IDENTITY 和 @@IDENTITY 相关的并行执行错误：http://support.microsoft.com/default.aspx?scid=kb;en-US;2019779 - David d C e Freitas

@DaviddCeFreitas -- 我很想了解这个错误，但是链接好像已经失效了（至少它正在抛出一个 ASP 错误）。 - rory.ap

其实，我找到了这个链接：https://support.microsoft.com/zh-cn/kb/2019779 - rory.ap

修复已经发布，正如旧的 KB 文章中所提到的。 - George Birbilis

Answer 1

• @@identity 函数返回在同一会话中创建的最后一个标识(identity)。
• scope_identity() 函数返回在同一会话和同一作用域(scope)中创建的最后一个标识(identity)。
• ident_current(name) 函数返回指定表(table)或视图(view)中任何会话创建的最后一个标识(identity)。
• identity() 函数不用于获取标识(identity)，而是用于在 select...into 查询中创建标识(identity)。

会话(session)是指数据库连接。作用域(scope)是指当前查询或存储过程。

如果表上有触发器(trigger)，导致查询插入记录时触发器在其他地方插入了另一个记录，那么scope_identity() 和 @@identity 函数不同。在这种情况下，scope_identity() 函数将返回查询创建的标识(identity)，而 @@identity 函数将返回触发器创建的标识(identity)。

因此，通常使用scope_identity()函数。

- Guffa

---

我选择这个答案，是因为它解释了“一个情况，在这种情况下 scope_identity() 和 @@identity...”一段话，让事情更加清晰明了。 - Orson

正如David Freitas上面提到的，scope_identity的实现中存在一个bug，因此我建议使用另一种方法，即OUTPUT子句。请参见下面的答案。 - Sebastian Meine

@Guffa - “会话是数据库连接”。如果您使用连接池，会话是否在连接之间保持不变？ - Dave Black

这是一个典范答案。特别是在处理SQL和SQL Server时可能会出现一些奇怪的情况，而这篇文章以通俗易懂的方式解释了问题，同时还相当详尽。它听起来不像是两个数据库专家之间交流的内容，这与许多其他Stack Exchange答案不同。 - Panzercrisis

@SebastianMeine：这在Rory.ap链接的kb文章中有提到：https://support.microsoft.com/en-us/kb/2019779 - Guffa

Answer 2

很好的问题。

• @@IDENTITY：返回在SQL连接（SPID）上生成的最后一个标识值。大多数情况下，这就是您想要的，但有时并不是这样（例如，在触发器响应INSERT时触发，并且触发器执行另一个INSERT语句）。

• SCOPE_IDENTITY()：返回当前范围（即存储过程、触发器、函数等）中生成的最后一个标识值。

• IDENT_CURRENT()：返回特定表的最后一个标识值。不要使用此方法获取INSERT的标识值，因为它容易出现竞争条件（即多个连接在同一表上插入行）。

• IDENTITY()：在声明表中的列为身份列时使用。

了解更多，请参见：http://msdn.microsoft.com/en-us/library/ms187342.aspx。

总之，如果要插入行，并且要知道刚刚插入的行的标识列的值，请始终使用SCOPE_IDENTITY()。

- Brannon

Answer 3

如果您理解作用域 (scope) 和会话 (session) 之间的区别，那么理解这些方法就非常容易了。

Adam Anderson 的一篇非常好的博客文章描述了这种差异:

Session 表示正在执行命令的当前连接。

Scope 表示一个命令的直接上下文。每个存储过程调用在其自己的作用域中执行，并且嵌套调用在调用过程的作用域内的嵌套作用域中执行。同样，从应用程序或 SSMS 执行的 SQL 命令在其自己的作用域内执行，如果该命令触发任何触发器，则每个触发器在其自己的嵌套作用域中执行。

因此，三种身份标识检索方法之间的区别如下:

@@identity 返回在 任何 作用域中生成的最后一个身份标识值。

scope_identity() 返回在 此 作用域中生成的最后一个身份标识值。

ident_current() 返回在 任何 会话和 任何 作用域中为特定表生成的最后一个身份标识值。

- Hemant Sakta

---

应该被接受的答案，因为它确切地定义了在 SQL 上下文中作用域和会话实际上是什么。 - Hashim Aziz

Answer 4

Scope指的是执行INSERT语句中SCOPE_IDENTITY()的代码上下文，而不是全局范围的@@IDENTITY。

CREATE TABLE Foo(
  ID INT IDENTITY(1,1),
  Dummy VARCHAR(100)
)

CREATE TABLE FooLog(
  ID INT IDENTITY(2,2),
  LogText VARCHAR(100)
)
go
CREATE TRIGGER InsertFoo ON Foo AFTER INSERT AS
BEGIN
  INSERT INTO FooLog (LogText) VALUES ('inserted Foo')
  INSERT INTO FooLog (LogText) SELECT Dummy FROM inserted
END

INSERT INTO Foo (Dummy) VALUES ('x')
SELECT SCOPE_IDENTITY(), @@IDENTITY 

结果不同。

- devio

Answer 5

Scope Identity: 存储过程内最后一条添加记录的标识。

@@Identity: 查询批处理内最后一条添加记录的标识，或者作为查询结果的最后一条添加记录的标识。例如，执行插入操作的存储过程然后触发一个插入记录的触发器，将返回触发器插入的记录的标识。

IdentCurrent: 分配给表的最后一个标识。

- Andrew

Answer 6

为了澄清与@@Identity相关的问题：

例如，如果您插入一个表格并且该表格有触发器执行插入操作，@@Identity将返回触发器中插入的id（如log_id），而scope_identity()将返回原始表中插入的id。

因此，如果您没有任何触发器，scope_identity()和@@identity将返回相同的值。如果您有触发器，则需要考虑您想要的值。

- Jonas Lincoln

Answer 7

这里有一段来自这本书的好解释：

对于SCOPE_IDENTITY和@@IDENTITY之间的区别，假设您有一个存储过程P1，其中包含三个语句：
- 生成新标识值的INSERT
- 调用存储过程P2，P2也有一个INSERT语句生成新的标识值
- 查询函数SCOPE_IDENTITY和@@IDENTITY的语句 SCOPE_IDENTITY函数将返回由P1（相同会话和范围）生成的值。@@IDENTITY函数将返回由P2（相同会话而不考虑范围）生成的值。

- Dmitriy Dokshin