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

What is the difference between Scope_Identity(), Identity(), @@Identity, and Ident_Current()?

提问by Tebo

I know Scope_Identity(), Identity(), @@Identity, and Ident_Current()all get the value of the identity column, but I would love to know the difference.

我知道Scope_Identity(), Identity(), @@Identity, 和Ident_Current()都得到了 identity 列的值，但我很想知道它们之间的区别。

Part of the controversy I'm having is what do they mean by scope as applied to these functions above?

我遇到的部分争议是，它们所指的适用于上述这些功能的范围是什么意思？

I would also love a simple example of different scenarios of using them?

我也喜欢使用它们的不同场景的简单示例？

回答by Guffa

• The @@identityfunction returns the last identity created in the same session.
• The scope_identity()function returns the last identity created in the same session and the same scope.
• The ident_current(name)returns the last identity created for a specific table or view in any session.
• The identity()function is not used to get an identity, it's used to create an identity in a select...intoquery.

• 该@@identity函数返回在同一会话中创建的最后一个身份。
• 该scope_identity()函数返回在同一会话和同一范围内创建的最后一个身份。
• 在ident_current(name)返回的任何会议的特定表或视图中创建的最后一个标识。
• 该identity()函数不用于获取身份，而是用于在select...into查询中创建身份。

The session is the database connection. The scope is the current query or the current stored procedure.

会话是数据库连接。范围是当前查询或当前存储过程。

A situation where the scope_identity()and the @@identityfunctions differ, is if you have a trigger on the table. If you have a query that inserts a record, causing the trigger to insert another record somewhere, the scope_identity()function will return the identity created by the query, while the @@identityfunction will return the identity created by the trigger.

一种情形的scope_identity()与@@identity功能的不同，就是如果你有在桌子上的触发器。如果您有一个插入记录的查询，导致触发器在某处插入另一条记录，则该scope_identity()函数将返回查询创建的标识，而该@@identity函数将返回触发器创建的标识。

So, normally you would use the scope_identity()function.

所以，通常你会使用这个scope_identity()函数。

回答by Brannon

Good question.

好问题。

• @@IDENTITY: returns the last identity value generated on your SQL connection (SPID). Most of the time it will be what you want, but sometimes it isn't (like when a trigger is fired in response to an INSERT, and the trigger executes another INSERTstatement).

• SCOPE_IDENTITY(): returns the last identity value generated in the current scope (i.e. stored procedure, trigger, function, etc).

• IDENT_CURRENT(): returns the last identity value for a specific table. Don't use this to get the identity value from an INSERT, it's subject to race conditions (i.e. multiple connections inserting rows on the same table).

• IDENTITY(): used when declaring a column in a table as an identity column.

• @@IDENTITY：返回在您的 SQL 连接 (SPID) 上生成的最后一个标识值。大多数时候它会是你想要的，但有时不是（比如当触发器被触发以响应INSERT，并且触发器执行另一个INSERT语句时）。

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

• IDENT_CURRENT(): 返回特定表的最后一个标识值。不要使用它从 中获取标识值INSERT，它会受到竞争条件的影响（即在同一个表中插入行的多个连接）。

• IDENTITY(): 用于将表中的列声明为标识列。

For more reference, see: http://msdn.microsoft.com/en-us/library/ms187342.aspx.

有关更多参考，请参阅：http: //msdn.microsoft.com/en-us/library/ms187342.aspx。

To summarize: if you are inserting rows, and you want to know the value of the identity column for the row youjust inserted, always use SCOPE_IDENTITY().

总结：如果您要插入行，你想知道该行的标识列的值，您刚插入，一直使用SCOPE_IDENTITY()。

回答by Hemant Sakta

If you understand the difference between scope and session then it will be very easy to understand these methods.

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

A very nice blog postby Adam Anderson describes this difference:

亚当安德森的一篇非常好的博客文章描述了这种差异：

Sessionmeans the current connection that's executing the command.

Scopemeans the immediate context of a command. Every stored procedure call executes in its own scope, and nested calls execute in a nested scope within the calling procedure's scope. Likewise, a SQL command executed from an application or SSMS executes in its own scope, and if that command fires any triggers, each trigger executes within its own nested scope.

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

范围是指命令的直接上下文。每个存储过程调用都在其自己的范围内执行，嵌套调用在调用过程范围内的嵌套范围内执行。同样，从应用程序或 SSMS 执行的 SQL 命令在其自己的范围内执行，如果该命令触发任何触发器，则每个触发器都在其自己的嵌套范围内执行。

Thus the differences between the three identity retrieval methods are as follows:

因此三种身份检索方法的区别如下：

@@identityreturns the last identity value generated in thissession but anyscope.

scope_identity()returns the last identity value generated in thissession and thisscope.

ident_current()returns the last identity value generated for a particular table in anysession and anyscope.

@@identity返回此会话中生成的最后一个标识值，但不包括任何范围。

scope_identity()返回在此会话和此范围中生成的最后一个标识值。

ident_current()返回为任何会话和任何范围内的特定表生成的最后一个标识值。

回答by devio

Scope means the code context that performs the INSERTstatement SCOPE_IDENTITY(), as opposed to the global scope of @@IDENTITY.

作用域是指执行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 

Gives different results.

给出不同的结果。

回答by Sebastian Meine

Because of the bug mentioned by @David Freitas and because of the incompatibility to the new Sequence feature that was introduced in 2012 I would recommend staying away from all three of these. Instead, you can use the OUTPUT clause to get the inserted identity value. The other advantage is that OUTPUT even works if you have inserted more than one row.

由于@David Freitas 提到的错误以及与 2012 年引入的新 Sequence 功能不兼容，我建议远离所有这三个。相反，您可以使用 OUTPUT 子句来获取插入的标识值。另一个优点是 OUTPUT 甚至可以在您插入多行时工作。

For details and examples see here: Identity Crisis

有关详细信息和示例，请参见此处：身份危机

回答by Jonas Lincoln

To clarify the problem with @@Identity:

用@@Identity以下方法澄清问题：

For instance, if you insert a table and that table has triggers doing inserts, @@Identitywill return the id from the insert in the trigger (a log_idor something), while scope_identity()will return the id from the insert in the original table.

例如，如果您插入一个表并且该表具有执行插入操作的触发器，@@Identity则将从触发器中的插入返回 id（alog_id或其他），同时scope_identity()从原始表中的插入返回 id。

So if you don't have any triggers, scope_identity()and @@identitywill return the same value. If you have triggers, you need to think about what value you'd like.

所以，如果你没有任何触发器，scope_identity()并且@@identity将返回相同的值。如果你有触发器，你需要考虑你想要什么值。

回答by Andrew

Scope Identity: Identity of last record added within the stored procedure being executed.

Scope Identity：正在执行的存储过程中添加的最后一条记录的标识。

@@Identity: Identity of last record added within the query batch, or as a result of the query e.g. a procedure that performs an insert, the then fires a trigger that then inserts a record will return the identity of the inserted record from the trigger.

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

IdentCurrent: The last identity allocated for the table.

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

回答by Dmitriy Dokshin

Here is another good explanation from the book:

这是书中另一个很好的解释：

As for the difference between SCOPE_IDENTITY and @@IDENTITY, suppose that you have a stored procedure P1 with three statements:
- An INSERT that generates a new identity value
- A call to a stored procedure P2 that also has an INSERT statement that generates a new identity value
- A statement that queries the functions SCOPE_IDENTITY and @@IDENTITY The SCOPE_IDENTITY function will return the value generated by P1 (same session and scope). The @@IDENTITY function will return the value generated by P2 (same session irrespective of scope).

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