IDENT_CURRENT ，@@IDENTITY， SCOPE_IDENTITY

最新推荐文章于 2021-11-30 15:50:50 发布

翻译最新推荐文章于 2021-11-30 15:50:50 发布 · 508 阅读

文章标签：

#insert #session #table #function #null

SQL 专栏收录该内容

18 篇文章

订阅专栏

本文详细解释了SQL Server中三种身份标识函数的功能与使用场景：IDENT_CURRENT返回特定表中的最后生成的身份值；@@IDENTITY返回当前会话中所有范围内最后生成的身份值；SCOPE_IDENTITY则返回当前会话及当前范围内最后生成的身份值。通过具体实例说明了这些函数在不同场景下的行为差异。

IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope.
@@IDENTITY returns the last identity value generated for any table in the current session, across all scopes.
SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.

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

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

For example, there are two tables, T1 and T2, and an INSERT trigger is defined on T1. When a row is inserted to T1, the trigger fires and inserts a row in T2. This scenario illustrates two scopes: the insert on T1, and the insert on T2 by the trigger.

Assuming that both T1 and T2 have identity columns, @@IDENTITY and SCOPE_IDENTITY will return different values at the end of an INSERT statement on T1. @@IDENTITY will return the last identity column value inserted across any scope in the current session. This is the value inserted in T2. SCOPE_IDENTITY() will return the IDENTITY value inserted in T1. This was the last insert that occurred in the same scope. The SCOPE_IDENTITY() function will return the null value if the function is invoked before any INSERT statements into an identity column occur in the scope.

USE AdventureWorks;
GO
SELECT IDENT_CURRENT ('Person.Address') AS Current_Identity;
GO