@@IDENTITY、SCOPE_IDENTITY()、OUTPUT以及其他检索最后标识的方法

Question

在插入数据后，检索主键标识字段的值有多种方法。

declare @t table (
    id int identity primary key,
    somecol datetime default getdate()
)
insert into @t
default values

select SCOPE_IDENTITY() --returns 1
select @@IDENTITY --returns 1

在插入后返回一个身份表：

Create Table #Testing (  
    id int identity,  
    somedate datetime default getdate()  
)  
insert into #Testing  
output inserted.*  
default values   

什么方法是适当或更好的？OUTPUT方法是否具有作用域安全性？

第二个代码片段来自SQL in the Wild。

- Terrapin

Answer 1

这要看你想做什么...

@@IDENTITY

返回连接中最后一个IDENTITY值，不管该值来自哪个表，也不管产生该值的语句的范围。 @@IDENTITY将返回当前会话中插入到表中的最后一个identity值。 @@IDENTITY仅限于当前会话，而不限于当前作用域。例如，如果您在一个表上有一个触发器，导致在另一个表中创建一个identity，那么您将得到最后创建的identity，即使是触发器创建它。

SCOPE_IDENTITY()

返回连接中与同一作用域中的语句相关联的最后一个IDENTITY值，不管该值来自哪个表。 SCOPE_IDENTITY()类似于@@IDENTITY，但它还将限制该值为当前作用域。换句话说，它将返回您明确创建的最后一个identity值，而不是由触发器或用户定义函数创建的任何identity。

IDENT_CURRENT()

返回表中最后一个IDENTITY值，不管产生该值的语句的连接和作用域如何。 IDENT_CURRENT仅限于指定的表，而不受连接或作用域的限制。

- mson

---

你没有描述如何使用OUTPUT，这是新版SQL Server中更为推荐的方法。它不仅具有作用域安全性，而且可以返回多个标识符，甚至是其他字段（如果未来需要进行处理）。 - HLGEM

@HLGEM，输出结果很好，但在你只需要一个单一的标识值时使用起来有些笨拙。 - Constantin

在哪里提到@@identity将被OUTPUT替换？ - gotqn

@HLGEM：请注意，如果我在表上有一个插入触发器（例如历史触发器），输出会中断。您需要将输出存储到一个表变量中，然后从该变量中进行选择。 - Stefan Steiger

@Domcha：是的，当你尝试运行一个带有插入触发器的输出时，它会告诉你。 - Stefan Steiger

Answer 2

请注意，scope_identity()和@@identity存在一个错误 - 请参见MS Connect：https://web.archive.org/web/20130412223343/https://connect.microsoft.com/SQLServer/feedback/details/328811/scope-identity-sometimes-returns-incorrect-value 引用（来自Microsoft）：

“我强烈建议在所有情况下都使用 OUTPUT 而不是 @@ IDENTITY 。 这只是读取标识和时间戳的最佳方法。”

编辑后添加：这个问题可能已经解决了。 Connect给我一个错误，但请参见：Scope_Identity()返回的值是否已修复？

- Simon D

---

我只是想提一下，上面的错误发生在使用并行处理时。但这不会影响单个插入操作的情况。以下是微软的一句话：“每当生成并行查询计划时，@@IDENTITY 和 SCOPE_IDENTITY() 不会被一致更新，不能依赖它们。” - Haydar

SCOPE_IDENTITY()怎么样？@@IDENTITY在更多的用例中似乎一直存在问题。 - user166390

Answer 3

当尝试获取刚插入的行的identity时，几乎没有什么理由使用除OUTPUT子句以外的任何东西。 OUTPUT子句是作用域和表安全的。

这里有一个简单的示例，在插入单个行后获取id...

DECLARE @Inserted AS TABLE (MyTableId INT);

INSERT [MyTable] (MyTableColOne, MyTableColTwo)
OUTPUT Inserted.MyTableId INTO @Inserted
VALUES ('Val1','Val2')

SELECT MyTableId FROM @Inserted

OUTPUT子句的详细文档：http://technet.microsoft.com/en-us/library/ms177564.aspx

---

-- table structure for example:     
CREATE TABLE MyTable (
    MyTableId int NOT NULL IDENTITY (1, 1),
    MyTableColOne varchar(50) NOT NULL,
    MyTableColTwo varchar(50) NOT NULL
)

- JeremyWeir

---

注意：当在游标中使用这种类型的东西时，请确保在每个迭代中从表变量中删除所有内容，因为 DECLARE 不会重新初始化。 - JeremyWeir

Answer 4

@@Identity是老派的方法。在所有情况下，使用SCOPE_IDENTITY()。请参阅MSDN了解使用@@IDENTITY的后果（它们很糟糕！）。

- jcollum

---

即使是SCOPE_IDENTITY()，在触发器执行自己的INSERT操作时也存在问题。（只能给出触发器插入到的最后一个表的标识） - MatBailie

那个链接中有示例代码似乎与你的说法相矛盾。我很确定触发器在不同的作用域内。 - jcollum

@Dems，那绝对不是真的。 - HLGEM

哇，对两年前的评论做出回应...是的，看起来当时我可能是喝醉了或者只是非常愚蠢才会添加那个评论。 - MatBailie

Answer 5

SCOPE_IDENTITY足以处理单行数据，除非您需要查看中间触发器的结果（为什么？），否则建议使用它。

对于多行数据，OUTPUT/OUTPUT INTO是您的新朋友，也是重新查找行并插入另一个表的替代方法。

- Cade Roux

Answer 6

对Godeke的回答进行小修正：

你需要担心的不仅仅是触发器。任何导致标识符被创建的嵌套操作，比如存储过程，都可能改变@@IDENTITY的值。

支持使用scope_identity...

- psasik

Answer 7

SQL Server 2005提供了另一种方法，详见SQL in the Wild。

这将允许您在插入后检索多个标识。以下是博客文章中的代码：

Create Table #Testing (  
    id int identity,  
    somedate datetime default getdate()  
)  
insert into #Testing  
output inserted.*  
default values

- Terrapin

---

我以前见过这个问题，但是我认为它可能不安全。也就是说，我可能会看到别人的插入。我不知道这是否属实。 - jcollum

我不确定。我会把这个加到问题里。 - Seibar

输出是作用域安全的。这是现在的首选方法。 - HLGEM

Answer 8

在使用 @@IDENTITY 时要小心...

http://dotnetgalactics.wordpress.com/2009/10/28/scope-identity-vs-identity/

- Pato