SQL Server - 获取插入行标识的最佳方法? [英] SQL Server - Best way to get identity of inserted row?

问题描述

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

What is the best way to get IDENTITY of inserted row?

我知道 @@IDENTITY 和 IDENT_CURRENT 和 SCOPE_IDENTITY，但不了解它们各自的优缺点.

I know about @@IDENTITY and IDENT_CURRENT and SCOPE_IDENTITY but don't understand the pros and cons attached to each.

有人可以解释一下不同之处以及我应该何时使用它们吗?

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

推荐答案

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

  • @@IDENTITY returns 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() 返回为当前会话和当前作用域中的任何表生成的最后一个标识值.通常是您想要使用的.

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

    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 Starbuck mentioned, "You could use this if you want to get the current IDENTITY value for a table that you have not inserted a record into."

    OUTPUT 子句INSERT 语句的 > 将允许您访问通过该语句插入的每一行.由于它仅限于特定语句，因此它比上述其他函数更直接.但是，它有点更详细(您需要插入到一个表变量/临时表中，然后查询它)，即使在语句被回滚的错误场景中，它也会给出结果.也就是说，如果您的查询使用并行执行计划，这是获取标识的唯一保证方法(除了关闭并行性).但是，它在之前触发器执行并且不能用于返回触发器生成的值.

    The OUTPUT clause of the INSERT statement will let you access every row that was inserted via that statement. Since it's scoped to the specific statement, it's more straightforward than 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 method for getting the identity (short of turning off parallelism). However, it is executed before triggers and cannot be used to return trigger-generated values.

    这篇关于SQL Server - 获取插入行标识的最佳方法?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！