获得插入行身份的最佳方法?

什么是获得最佳方式IDENTITY插排的?

我知道@@IDENTITYIDENT_CURRENTSCOPE_IDENTITY但不了解它们各自的优缺点。

有人可以解释这些差异以及何时使用它们吗?

答案

  • @@IDENTITY返回在所有范围内为当前会话中的任何表生成的最后一个标识值。 您需要小心 ,因为它是跨作用域的。您可以从触发器而不是当前语句中获取值。

  • SCOPE_IDENTITY()返回为当前会话和当前范围中的任何表生成的最后一个标识值。 通常您要使用什么

  • IDENT_CURRENT('tableName')返回在任何会话和任何作用域中为特定表生成的最后一个标识值。这可以让您指定要从哪个表获取值,以防上述两个表不是您真正需要的表( 非常少见 )。另外,正如 @ Guy Starbuck提到的那样,“如果要获取未插入记录的表的当前 IDENTITY 值,则可以使用它。”

  • INSERT语句的OUTPUT子句可让您访问通过该语句插入的每一行。由于它是针对特定语句的,因此它比上面的其他函数更直接 。但是,它有些冗长 (您需要将其插入到表变量 / 临时表中,然后对其进行查询),即使在语句回滚的错误情况下,它也可以提供结果。就是说,如果您的查询使用并行执行计划,则这是唯一获得身份的保证方法 (缺少关闭并行性)。但是,它触发器之前执行不能用于返回触发器生成的值。

我相信检索插入的 ID 的最安全,最准确的方法是使用 output 子句。

例如(摘自以下MSDN文章)

USE AdventureWorks2008R2;
GO
DECLARE @MyTableVar table( NewScrapReasonID smallint,
                           Name varchar(50),
                           ModifiedDate datetime);
INSERT Production.ScrapReason
    OUTPUT INSERTED.ScrapReasonID, INSERTED.Name, INSERTED.ModifiedDate
        INTO @MyTableVar
VALUES (N'Operator error', GETDATE());

--Display the result set of the table variable.
SELECT NewScrapReasonID, Name, ModifiedDate FROM @MyTableVar;
--Display the result set of the table.
SELECT ScrapReasonID, Name, ModifiedDate 
FROM Production.ScrapReason;
GO

我说的是与其他人相同的话,所以每个人都是对的,我只是想让事情变得更清楚。

@@IDENTITY返回客户端连接到数据库所插入的最后一个事物的 ID。
在大多数情况下,这可以正常工作,但有时会触发一个触发器,插入您不知道的新行,并且您将从新行中获取 ID,而不是您想要的行

SCOPE_IDENTITY()解决了这个问题。它返回发送到数据库的 SQL 代码中插入的最后一件事的 ID。如果触发器创建了额外的行,它们将不会导致返回错误的值。万岁

IDENT_CURRENT返回任何人插入的最后一个 ID。如果某个其他应用恰巧在不幸的时间插入另一行,则您将获得该行的 ID,而不是您的 ID。

如果您想安全使用它,请始终使用SCOPE_IDENTITY() 。如果您坚持使用@@IDENTITY并且以后有人决定添加触发器,则所有代码都会中断。

获取新插入的行的标识的最佳(读取:最安全)方法是使用output子句:

create table TableWithIdentity
           ( IdentityColumnName int identity(1, 1) not null primary key,
             ... )

-- type of this table's column must match the type of the
-- identity column of the table you'll be inserting into
declare @IdentityOutput table ( ID int )

insert TableWithIdentity
     ( ... )
output inserted.IdentityColumnName into @IdentityOutput
values
     ( ... )

select @IdentityValue = (select ID from @IdentityOutput)

SELECT CAST(scope_identity() AS int);

到您插入 sql 语句的末尾,然后

NewId = command.ExecuteScalar()

将检索它。

当您使用实体框架时,它在内部使用OUTPUT技术返回新插入的 ID 值

DECLARE @generated_keys table([Id] uniqueidentifier)

INSERT INTO TurboEncabulators(StatorSlots)
OUTPUT inserted.TurboEncabulatorID INTO @generated_keys
VALUES('Malleable logarithmic casing');

SELECT t.[TurboEncabulatorID ]
FROM @generated_keys AS g 
   JOIN dbo.TurboEncabulators AS t 
   ON g.Id = t.TurboEncabulatorID 
WHERE @@ROWCOUNT > 0

输出结果存储在临时表变量中,再联接回表,并从表中返回行值。

注意:我不知道为什么 EF 会将临时表内部联接回真实表(在什么情况下两者不匹配)。

但这就是 EF 所做的。

此技术( OUTPUT )仅在 SQL Server 2008 或更高版本上可用。

编辑 - 加入的原因

该实体框架连接回原来的表,而不是简单地使用的原因OUTPUT值是因为 EF 也使用这种技术来获取rowversion新插入的行的。

您可以通过使用开放式并发在实体框架模型使用Timestamp属性: 🕗

public class TurboEncabulator
{
   public String StatorSlots)

   [Timestamp]
   public byte[] RowVersion { get; set; }
}

执行此操作时,Entity Framework 将需要新插入的行的rowversion

DECLARE @generated_keys table([Id] uniqueidentifier)

INSERT INTO TurboEncabulators(StatorSlots)
OUTPUT inserted.TurboEncabulatorID INTO @generated_keys
VALUES('Malleable logarithmic casing');

SELECT t.[TurboEncabulatorID], t.[RowVersion]
FROM @generated_keys AS g 
   JOIN dbo.TurboEncabulators AS t 
   ON g.Id = t.TurboEncabulatorID 
WHERE @@ROWCOUNT > 0

为了检索此Timetsamp不能使用OUTPUT子句。

那是因为如果表上有一个触发器,那么您输出的任何Timestamp都会是错误的:

  • 初始插入。时间戳:1
  • OUTPUT 子句输出时间戳:1
  • 触发器修改行。时间戳记:2

如果表上有触发器,则返回的时间戳永远不会正确。因此,您必须使用单独的SELECT

而且,即使你愿意承受不正确的 rowversion,另一个原因执行单独的SELECT是,你不能输出rowversion到表变量:

DECLARE @generated_keys table([Id] uniqueidentifier, [Rowversion] timestamp)

INSERT INTO TurboEncabulators(StatorSlots)
OUTPUT inserted.TurboEncabulatorID, inserted.Rowversion INTO @generated_keys
VALUES('Malleable logarithmic casing');

这样做的第三个原因是对称。在带有触发器的表上执行UPDATE时, 不能使用OUTPUT子句。不支持尝试使用OUTPUT进行UPDATE ,这将导致错误:

唯一的方法是使用后续的SELECT语句:

UPDATE TurboEncabulators
SET StatorSlots = 'Lotus-O deltoid type'
WHERE ((TurboEncabulatorID = 1) AND (RowVersion = 792))

SELECT RowVersion
FROM TurboEncabulators
WHERE @@ROWCOUNT > 0 AND TurboEncabulatorID = 1

MSDN

@@ IDENTITY,SCOPE_IDENTITY 和 IDENT_CURRENT 是相似的函数,因为它们返回插入到表的 IDENTITY 列中的最后一个值。

@@ IDENTITY 和 SCOPE_IDENTITY 将返回当前会话中任何表中生成的最后一个标识值。但是,SCOPE_IDENTITY 仅在当前范围内返回该值。 @@ IDENTITY 不限于特定范围。

IDENT_CURRENT 不受范围和会话的限制;它仅限于指定的表。 IDENT_CURRENT 返回为任何会话和任何作用域中的特定表生成的标识值。有关更多信息,请参见 IDENT_CURRENT。

@@ IDENTITY是使用当前 SQL 连接插入的最后一个身份。这是从插入存储过程返回的一个好值,在该存储过程中,您只需要为新记录插入标识,而不必担心之后是否添加了更多行。

SCOPE_IDENTITY是使用当前 SQL 连接在当前作用域中插入的最后一个标识,也就是说,如果在插入后基于触发器插入了第二个 IDENTITY,它将不会反映在 SCOPE_IDENTITY 中,仅反映您执行的插入。坦白说,我从来没有理由使用它。

IDENT_CURRENT(表名)是最后插入的标识,无论连接或作用域如何。如果要获取未插入记录的表的当前 IDENTITY 值,可以使用此方法。

我不能说其他版本的 SQL Server,但在 2012 年,直接输出就可以了。您无需为临时表而烦恼。

INSERT INTO MyTable
OUTPUT INSERTED.ID
VALUES (...)

顺便说一句,该技术在插入多行时也适用。

INSERT INTO MyTable
OUTPUT INSERTED.ID
VALUES
    (...),
    (...),
    (...)

输出量

ID
2
3
4

总是使用 scope_identity(),就不需要其他任何东西了。