Scope_Identity()、Identity()、@@Identity 和 Ident_Current() 之间有什么区别?

2021-12-01 00:00:00 sql identity sql-server

我知道 Scope_Identity()、Identity()、@@Identity 和 Ident_Current() 所有获取标识列的值，但我很想知道其中的区别.

I know Scope_Identity(), Identity(), @@Identity, and Ident_Current() all get the value of the identity column, but I would love to know the difference.

我遇到的部分争议是，它们所指的适用于上述这些功能的范围是什么意思?

Part of the controversy I'm having is what do they mean by scope as applied to these functions above?

我也喜欢使用它们的不同场景的简单示例?

I would also love a simple example of different scenarios of using them?

推荐答案

  • @@identity 函数返回在同一会话中创建的最后一个身份.
  • scope_identity() 函数返回在同一会话和同一范围内创建的最后一个身份.
  • ident_current(name) 返回为任何会话中的特定表或视图创建的最后一个标识.
  • identity() 函数不用于获取身份，而是用于在 select...into 查询中创建身份.

    • The @@identity function returns the last identity created in the same session.
    • The scope_identity() function returns the last identity created in the same session and the same scope.
    • The ident_current(name) returns the last identity created for a specific table or view in any session.
    • The identity() function is not used to get an identity, it's used to create an identity in a select...into query.

      • 会话是数据库连接.作用域是当前查询或当前存储过程.

      The session is the database connection. The scope is the current query or the current stored procedure.

      scope_identity() 和 @@identity 函数不同的情况是，如果您在表上有触发器.如果您有一个插入记录的查询，导致触发器在某处插入另一条记录，scope_identity() 函数将返回查询创建的标识，而 @@identity 函数将返回触发器创建的标识.

      A situation where the scope_identity() and the @@identity functions differ, is if you have a trigger on the table. If you have a query that inserts a record, causing the trigger to insert another record somewhere, the scope_identity() function will return the identity created by the query, while the @@identity function will return the identity created by the trigger.

      因此，通常您会使用 scope_identity() 函数.

      So, normally you would use the scope_identity() function.

相关文章