Scope_Identity()、Identity()、@@Identity和Ident_Current()之间的区别是什么?

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?

我也喜欢用不同场景的简单例子?

8 个解决方案

#1

• The @@identity function returns the last identity created in the same session.
• @@identity函数返回在同一会话中创建的最后一个标识。
• The scope_identity() function returns the last identity created in the same session and the same scope.
• scope_identity()函数返回在同一会话和相同范围中创建的最后一个标识。
• The ident_current(name) returns the last identity created for a specific table or view in any session.
• ident_current(name)返回为任何会话中的特定表或视图创建的最后一个标识。
• The identity() function is not used to get an identity, it's used to create an identity in a select...into query.
• 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 @@identity functions 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 @@identity function 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()函数。

#2

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 INSERT statement).

  返回在SQL连接上生成的最后一个标识值(SPID)。大多数情况下，它将是您想要的，但有时不是(比如在响应插入时触发触发器，触发器执行另一个INSERT语句)。

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

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

• 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).

  IDENT_CURRENT():返回特定表的最后一个标识值。不要使用它从INSERT中获取标识值，它受竞争条件的限制(例如，在同一个表中插入多个连接)。

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

  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 you just inserted, always use SCOPE_IDENTITY().

总结:如果您正在插入行，并且想要知道您刚刚插入的行的标识列的值，请始终使用SCOPE_IDENTITY()。

#3

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

如果您理解范围和会话之间的区别，那么就很容易理解这些方法。

A very nice blog post by Adam Anderson describes this difference:

亚当·安德森(Adam Anderson)写了一篇很棒的博客文章，描述了这种不同:

Session means the current connection that's executing the command.

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

Scope means 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:

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

@@identity returns the last identity value generated in this session but any scope.

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

scope_identity() returns the last identity value generated in this session and this scope.

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

ident_current() returns the last identity value generated for a particular table in any session and any scope.

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

#4

Scope means the code context that performs the INSERT statement 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.

给出了不同的结果。

#5

To clarify the problem with @@Identity:

要澄清@@Identity问题:

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

例如，如果您插入一个表，并且该表具有执行插入的触发器，@@Identity将从触发器中的insert (log_id或其他)返回id，而scope_identity()将从原始表中的insert返回id。

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

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

#6

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提到的bug，以及与2012年引入的新序列特性不兼容，我建议您远离这三种特性。相反，您可以使用OUTPUT子句获取插入的标识值。另一个优点是，如果插入了多个行，输出甚至可以工作。

For details and examples see here: Identity Crisis

有关细节和示例，请参见这里:身份危机

#7

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,也有一个INSERT语句,生成一个新的标识价值——一份声明中,查询功能SCOPE_IDENTITY和@@IDENTITY SCOPE_IDENTITY函数将返回生成的值P1(同一会话和范围)。@@IDENTITY函数将返回P2生成的值(与范围无关的会话)。

#8

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

范围标识:在正在执行的存储过程中添加的最后一条记录的标识。

@@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:分配给表的最后一个标识。