哪一个是获取我刚刚通过插入生成的标识值的最佳选择？这些语句对性能有何影响？



SCOPE_IDENTITY()
聚合函数MAX()

SELECT TOP 1 TableName的IdentityColumn ORDER BY IdentityColumn DESC



评论

使用postgreSQL，您可以从书架中获得它postgresql.org/docs/9.1/static/sql-insert.html
Leftfield选项-如果表中有一个Guid列，并且可以生成一个新的Guid并将其插入到插入过程中的新列中，则可以选择带有该Guid的行以提取生成的int标识。 >

#1 楼

如果要插入单行并要检索生成的ID，请使用SCOPE_IDENTITY()。

CREATE TABLE #a(identity_column INT IDENTITY(1,1), x CHAR(1));

INSERT #a(x) VALUES('a');

SELECT SCOPE_IDENTITY();


结果：

----
1


如果要插入多行并且需要检索生成的ID集，请使用OUTPUT子句。

INSERT #a(x) 
  OUTPUT inserted.identity_column 
  VALUES('b'),('c');


结果：

----
2
3



为什么这是最好的更快选择？


除了性能，这些是唯一可以保证的。更正默认隔离级别和/或与多个用户。即使您忽略了正确性方面，SQL Server也会在内存中将插入的值保存在SCOPE_IDENTITY()中，因此自然地，这比对表或对系统表进行独立的查询要快。

无视正确性就像告诉邮递员他在完成今天的邮件方面做得很好-他完成路线的时间比平均时间快10分钟，问题是，没有邮件被发送到右边的房子。

请勿使用以下任何一种产品：



@@IDENTITY-例如，因为这不能在所有情况下使用当带有标识列的表具有触发器，并且该触发器也插入具有自己标识列的另一个表中时，您将获得错误的值。

IDENT_CURRENT()-我在这里详细介绍注释也很有用，但是从本质上讲，在并发情况下，您常常会得到错误的答案。

MAX()或TOP 1-您必须使用可序列化的隔离保护两个语句，以确保MAX()你得到的不是别人的。这比仅使用SCOPE_IDENTITY()要昂贵得多。

这些功能在插入两行或更多行并需要生成所有标识值时也会失败-您唯一的选择是OUTPUT子句。 >

评论

我的记忆中又触发了一个问题。每当我们需要获取任何会话或用户在特定表中生成的最后一个身份时，唯一正确且最佳的方法就是该列的MAX（）？

–AA.SC
2015年12月31日5:56


当我在表中插入多行时，SCOPE_IDENTITY（）将始终返回最后一次生成的标识怎么办？如果列是主键而不是身份列怎么办？

–AA.SC
15年12月31日在7:12
@ AA.SC是的，它将返回最后一个。如果不是身份列，则否，这些功能都不起作用。在这种情况下，PK值从何而来？

–亚伦·伯特兰（Aaron Bertrand）
15年12月31日在11:55
我已经在我们的应用程序的列中看到了这一点，其中该列是INT类型，并且开发人员在需要插入新记录时会使用MAX（columnname）+1

–AA.SC
2015年12月31日12:01


然后他们已经知道他们刚刚插入了什么值。 SQL Server没有任何办法告诉您（事实发生后，您就不能再依赖于拉MAX，除非您完全隔离了整个事务，这对性能或并发性都不利）。

–亚伦·伯特兰（Aaron Bertrand）
2015年12月31日12:04


#2 楼

除了性能外，它们的含义也有很大不同。

SCOPE_IDENTITY()将为您提供直接插入当前范围内的任何表中的最后一个标识值（作用域=批处理，存储过程等，但不在此范围内，例如，当前作用域触发的触发器。）

IDENT_CURRENT()将为您提供任何用户从任何作用域插入到特定表中的最后一个标识值。

@@IDENTITY为您提供由最新INSERT语句为当前连接生成的最后一个标识值，而不管表或作用域如何。 （附带说明：Access使用此功能，因此触发器在将值插入具有标识列的表中时会遇到一些问题。）

如果表的值为负数，则使用MAX()或TOP 1会给您完全错误的结果标识步骤，或在播放中插入了带有SET IDENTITY_INSERT的行。这是一个演示所有这些内容的脚本：

CREATE TABLE ReverseIdent (
    id int IDENTITY(9000,-1) NOT NULL PRIMARY KEY CLUSTERED,
    data char(4)
)

INSERT INTO ReverseIdent (data)
VALUES ('a'), ('b'), ('c')

SELECT * FROM ReverseIdent

SELECT IDENT_CURRENT('ReverseIdent') --8998
SELECT MAX(id) FROM ReverseIdent --9000

SET IDENTITY_INSERT ReverseIdent ON

INSERT INTO ReverseIdent (id, data)
VALUES (9005, 'd')

SET IDENTITY_INSERT ReverseIdent OFF

SELECT IDENT_CURRENT('ReverseIdent') --8998
SELECT MAX(id) FROM ReverseIdent --9005


摘要：坚持使用SCOPE_IDENTITY()，IDENT_CURRENT()或@@IDENTITY，并确保您使用的是返回实际值的脚本需要。

评论

当您自己的脚本证明它们输出错误的结果时，为什么鼓励使用IDENT_CURRENT（）和@@ IDENTITY？

–亚伦·伯特兰（Aaron Bertrand）
2015年12月30日14:51


@AaronBertrand我不确定我是否遵循。生成的最后一个标识值是8998（注意步骤是-1），这就是IDENT_CURRENT（）返回的值。 MAX（）永远不会返回超出第一行的正确值，因为id会向后计数，并且在IDENTITY_INSERT启用的情况下，9005不是生成的标识值，因此不会由IDENT_CURRENT（）反映出来。但是，如果您真正追求的是SCOPE_IDENTITY（）返回的结果，则可能会返回“不正确”的结果。选择适合该工作的工具。

–db2
15年12月30日在16:37
OP似乎在他们插入的标识值之后-在这种情况下8998不正确。我认为您提到的边缘情况（向后递增和IDENTITY_INSERT开启）甚至进一步反对使用IDENT_CURRENT，并且@@ IDENTITY绝对不应该使用，因为存在触发危险（现在或以后添加）。我仍在努力理解为什么IDENT_CURRENT将成为OP想要使用的（特别是在并发状态下），或者为什么在存在更可靠的方法时任何人都将使用@@ IDENTITY的原因。

–亚伦·伯特兰（Aaron Bertrand）
2015年12月30日在16:42


@AaronBertrand这个问题尚不能100％明确，期望的结果是当前作用域中的最后一个插入项（在这方面，选项1与2和3有所不同），所以我认为描述这两者以及它们的方式是一个好主意。不同。但是我确实同意@@ IDENTITY几乎从来不是获取生成的身份值的理想方法。要点是MAX（）或TOP 1就像IDENT_CURRENT（）的一个不太可靠的版本，如果您了解它的作用，那么它是一个非常好的函数。可能对维护工作或其他有用。

–db2
15年12月30日在17:54