sql - 获得插入行身份的最佳方法？(Best way to get identity of inserted row?)

Question

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

(什么是获得最佳方式IDENTITY插排的？)

I know about @@IDENTITY and IDENT_CURRENT and SCOPE_IDENTITY but 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?

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

  ask by Oded translate from so

Answer 1

• @@IDENTITY returns the last identity value generated for any table in the current session, across all scopes.

  (@@IDENTITY返回在所有范围内为当前会话中的任何表生成的最后一个标识值。)

  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.

  (SCOPE_IDENTITY()返回为当前会话和当前范围中的任何表生成的最后一个标识值。)

  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.

  (IDENT_CURRENT('tableName')返回在任何会话和任何作用域中为特定表生成的最后一个标识值。)

  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."

  (另外，正如@ Guy Starbuck提到的那样，“如果要获取未插入记录的表的当前IDENTITY值，则可以使用它。”)

• The OUTPUT clause of the INSERT statement will let you access every row that was inserted via that statement.

  (INSERT语句的OUTPUT子句可让您访问通过该语句插入的每一行。)

  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.

  (但是，它在触发器之前执行，不能用于返回触发器生成的值。)