SQL 获取插入行标识的最佳方法？

Best way to get identity of inserted row?

提问by Oded

What is the best way to get IDENTITYof inserted row?

获取IDENTITY插入行的最佳方法是什么？

I know about @@IDENTITYand IDENT_CURRENTand SCOPE_IDENTITYbut don't understand the pros and cons attached to each.

我知道@@IDENTITY和IDENT_CURRENT和SCOPE_IDENTITY，但不明白连接到每个利弊。

Can someone please explain the differences and when I should be using each?

有人可以解释一下差异以及我应该何时使用它们吗？

回答by bdukes

• @@IDENTITYreturns the last identity value generated for any table in the current session, across all scopes. You need to be careful here, since it's across scopes. You could get a value from a trigger, instead of your current statement.

• SCOPE_IDENTITY()returns the last identity value generated for any table in the current session and the current scope. Generally what you want to use.

• IDENT_CURRENT('tableName')returns the last identity value generated for a specific table in any session and any scope. This lets you specify which table you want the value from, in case the two above aren't quite what you need (very rare). Also, as @Guy Starbuckmentioned, "You could use this if you want to get the current IDENTITY value for a table that you have not inserted a record into."

• The OUTPUTclauseof the INSERTstatement will let you access every row that was inserted via that statement. Since it's scoped to the specific statement, it's more straightforwardthan the other functions above. However, it's a little more verbose(you'll need to insert into a table variable/temp table and then query that) and it gives results even in an error scenario where the statement is rolled back. That said, if your query uses a parallel execution plan, this is the only guaranteed methodfor getting the identity (short of turning off parallelism). However, it is executed beforetriggers and cannot be used to return trigger-generated values.

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

• SCOPE_IDENTITY()返回为当前会话和当前作用域中的任何表生成的最后一个标识值。 一般你想用什么。

• IDENT_CURRENT('tableName')返回为任何会话和任何范围内的特定表生成的最后一个标识值。这使您可以指定要从中获取值的表，以防上面的两个表不是您所需要的（非常罕见）。此外，正如@Guy Starbuck 所提到的，“如果您想获取尚未插入记录的表的当前 IDENTITY 值，则可以使用它。”

• 该OUTPUT条款的的INSERT声明将让您访问每一个经该语句插入行。由于它的作用域是特定的语句，因此它比上面的其他函数更直接。但是，它有点冗长（您需要插入到表变量/临时表中，然后对其进行查询），即使在语句被回滚的错误场景中，它也会给出结果。也就是说，如果您的查询使用并行执行计划，这是获取标识的唯一保证方法（除了关闭并行性）。但是，它在触发器之前执行，不能用于返回触发器生成的值。

回答by Orry

I believe the safest and most accurate method of retrieving the inserted id would be using the output clause.

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

for example (taken from the following MSDNarticle)

例如（取自以下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

回答by Orion Edwards

I'm saying the same thing as the other guys, so everyone's correct, I'm just trying to make it more clear.

我和其他人说同样的话，所以每个人都是正确的，我只是想让它更清楚。

@@IDENTITYreturns the id of the last thing that was inserted by your client's connection to the database.
Most of the time this works fine, but sometimes a trigger will go and insert a new row that you don't know about, and you'll get the ID from this new row, instead of the one you want

@@IDENTITY返回由您的客户端与数据库的连接插入的最后一个事物的 ID。
大多数时候这工作正常，但有时触发器会插入一个你不知道的新行，你将从这个新行中获得 ID，而不是你想要的

SCOPE_IDENTITY()solves this problem. It returns the id of the last thing that you insertedin the SQL code you sentto the database. If triggers go and create extra rows, they won't cause the wrong value to get returned. Hooray

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

IDENT_CURRENTreturns the last ID that was inserted by anyone. If some other app happens to insert another row at an unforunate time, you'll get the ID of that row instead of your one.

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

If you want to play it safe, always use SCOPE_IDENTITY(). If you stick with @@IDENTITYand someone decides to add a trigger later on, all your code will break.

如果您想安全使用，请始终使用SCOPE_IDENTITY(). 如果您坚持使用@@IDENTITY并且有人决定稍后添加触发器，那么您的所有代码都会中断。

回答by Ian Kemp

The best (read: safest) way to get the identity of a newly-inserted row is by using the outputclause:

获取新插入行标识的最佳（阅读：最安全）方法是使用以下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)

回答by Jim

Add

添加

SELECT CAST(scope_identity() AS int);

to the end of your insert sql statement, then

到插入 sql 语句的末尾，然后

NewId = command.ExecuteScalar()

will retrieve it.

将检索它。

回答by Ian Boyd

When you use Entity Framework, it internally uses the OUTPUTtechnique to return the newly inserted ID value

当您使用实体框架时，它在内部使用该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

The output results are stored in a temporary table variable, joined back to the table, and return the row value out of the table.

输出结果存储在临时表变量中，连接回表，并返回表外的行值。

Note: I have no idea why EF would inner join the ephemeral table back to the real table (under what circumstances would the two not match).

注意：我不知道为什么 EF 会将临时表内部连接回真实表（在什么情况下两者不匹配）。

But that's what EF does.

但这就是EF所做的。

This technique (OUTPUT) is only available on SQL Server 2008 or newer.

此技术 ( OUTPUT) 仅适用于 SQL Server 2008 或更新版本。

Edit- The reason for the join

编辑- 加入的原因

The reason that Entity Framework joins back to the original table, rather than simply use the OUTPUTvalues is because EF also uses this technique to get the rowversionof a newly inserted row.

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

You can use optimistic concurrency in your entity framework models by using the Timestampattribute:

您可以使用以下属性在实体框架模型中使用乐观并发：Timestamp

public class TurboEncabulator
{
   public String StatorSlots)

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

When you do this, Entity Framework will need the rowversionof the newly inserted row:

执行此操作时，实体框架将需要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

And in order to retrieve this Timetsampyou cannotuse an OUTPUTclause.

为了检索它，Timetsamp您不能使用OUTPUT子句。

That's because if there's a trigger on the table, any Timestampyou OUTPUT will be wrong:

那是因为如果表上有触发器，任何Timestamp你的 OUTPUT 都会出错：

• Initial insert. Timestamp: 1
• OUTPUT clause outputs timestamp: 1
• trigger modifies row. Timestamp: 2

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

The returned timestamp will neverbe correct if you have a trigger on the table. So you mustuse a separate SELECT.

如果表上有触发器，则返回的时间戳将永远不会正确。所以你必须使用一个单独的SELECT.

And even if you were willing to suffer the incorrect rowversion, the other reason to perform a separate SELECTis that you cannot OUTPUT a rowversioninto a table variable:

即使您愿意忍受不正确的行版本，执行单独的另一个原因SELECT是您无法rowversion将 a输出到表变量中：

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

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

The third reason to do it is for symmetry. When performing an UPDATEon a table with a trigger, you cannotuse an OUTPUTclause. Trying do UPDATEwith an OUTPUTis not supported, and will give an error:

这样做的第三个原因是为了对称。UPDATE使用触发器对表执行 an时，不能使用OUTPUT子句。不支持尝试UPDATE使用 an OUTPUT，并且会出现错误：

• Cannot use UPDATE with OUTPUT clause when a trigger is on the table

• 当触发器在表上时，不能将 UPDATE 与 OUTPUT 子句一起使用

The only way to do it is with a follow-up SELECTstatement:

唯一的方法是使用后续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

回答by Jakub ?turc

MSDN

MSDN

@@IDENTITY, SCOPE_IDENTITY, and IDENT_CURRENT are similar functions in that they return the last value inserted into the IDENTITY column of a table.

@@IDENTITY and SCOPE_IDENTITY will return the last identity value generated in any table in the current session. However, SCOPE_IDENTITY returns the value only within the current scope; @@IDENTITY is not limited to a specific scope.

IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the identity value generated for a specific table in any session and any scope. For more information, see IDENT_CURRENT.

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

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

IDENT_CURRENT 不受范围和会话的限制；它仅限于指定的表。IDENT_CURRENT 返回为任何会话和任何范围内的特定表生成的标识值。有关详细信息，请参阅 IDENT_CURRENT。

• IDENT_CURRENTis a function which takes a table as a argument.
• @@IDENTITYmay return confusing result when you have an trigger on the table
• SCOPE_IDENTITYis your hero most of the time.

• IDENT_CURRENT是一个将表作为参数的函数。
• @@IDENTITY在表上有触发器时可能会返回令人困惑的结果
• SCOPE_IDENTITY大部分时间都是您的英雄。

回答by Guy Starbuck

@@IDENTITYis the last identity inserted using the current SQL Connection. This is a good value to return from an insert stored procedure, where you just need the identity inserted for your new record, and don't care if more rows were added afterward.

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

SCOPE_IDENTITYis the last identity inserted using the current SQL Connection, and in the current scope -- that is, if there was a second IDENTITY inserted based on a trigger after your insert, it would not be reflected in SCOPE_IDENTITY, only the insert you performed. Frankly, I have never had a reason to use this.

SCOPE_IDENTITY是使用当前 SQL 连接插入的最后一个身份，并且在当前范围内——也就是说，如果在插入后根据触发器插入了第二个 IDENTITY，它不会反映在 SCOPE_IDENTITY 中，只有您执行的插入. 坦率地说，我从来没有理由使用它。

IDENT_CURRENT(tablename)is the last identity inserted regardless of connection or scope. You could use this if you want to get the current IDENTITY value for a table that you have not inserted a record into.

IDENT_CURRENT(tablename)是最后插入的标识，无论连接或范围如何。如果要获取尚未插入记录的表的当前 IDENTITY 值，则可以使用它。

回答by MarredCheese

I can't speak to other versions of SQL Server, but in 2012, outputting directly works just fine. You don't need to bother with a temporary table.

我无法与其他版本的 SQL Server 交谈，但在 2012 年，直接输出效果很好。您无需为临时表而烦恼。

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

By the way, this technique also works when inserting multiple rows.

顺便说一下，这种技术在插入多行时也有效。

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

Output

输出

ID
2
3
4

回答by erikkallen

ALWAYSuse scope_identity(), there's NEVER a need for anything else.

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