What are the Differences Between `@@IDENTITY`, `SCOPE_IDENTITY()`, `IDENT_CURRENT()`, and `IDENTITY()` in SQL Server?

In-depth understanding of the @@IDENTITY, SCOPE_IDENTITY(), IDENT_CURRENT() and IDENTITY() functions in SQL Server

In SQL database programming, getting the last inserted identity value is a critical task. A variety of functions are provided for this purpose, each with its own nuances. SCOPE_IDENTITY(), IDENTITY(), @@IDENTITY, and IDENT_CURRENT() are a few of the most commonly used functions, but the differences between them can be confusing.

Understanding Scope

Understanding the concept of "scope" is key to understanding the differences between these functions. In SQL, scope refers to the level at which a query or block of code executes. There are two main scopes:

• Session scope: The context of a single database connection.
• Query scope: The context of a single query or stored procedure.

Function difference

Based on scope and function, here is the breakdown of each function:

• @@IDENTITY: Returns the last identity value created in the same session. This function does not depend on the scope of the query or stored procedure and is typically used to retrieve global identity values.
• SCOPE_IDENTITY(): Returns the last identity value created within the same session and scope. Unlike @@IDENTITY, this function only considers the context of the current query or stored procedure.
• IDENT_CURRENT(name): Returns the last identity value created for a specific table or view, regardless of session or query scope. This function can be used to retrieve identity values ​​across different queries and sessions.
• IDENTITY(): Unlike the other three functions, IDENTITY() is mainly used to generate identity values ​​in SELECT...INTO queries. It does not retrieve existing identity values.

Usage scenarios

To illustrate these differences, consider the following scenario:

• Example 1: Insert a record with an identity column and retrieve it using @@IDENTITY.

<code class="language-sql">INSERT INTO MyTable (Name) OUTPUT INSERTED.ID INTO @Identity;
PRINT @Identity;  -- 打印全局插入的标识值</code>

• Example 2: Create a trigger on MyTable to insert a record in another table and retrieve the identity value from both tables.

<code class="language-sql">CREATE TRIGGER MyTrigger ON MyTable AFTER INSERT AS
INSERT INTO OtherTable (Name) VALUES (NEW.Name);

INSERT INTO MyTable (Name) VALUES ('Test');

SELECT @@identity AS MyIdentity;  -- 打印在MyTable中插入的标识值
SELECT scope_identity() AS TriggerIdentity;  -- 打印触发器在OtherTable中插入的标识值</code>

In these scenarios, @@IDENTITY and SCOPE_IDENTITY() behave differently due to the involvement of the trigger.

Best practice suggestions

In most cases, SCOPE_IDENTITY() is the recommended function to retrieve the last identity value created. It provides the most reliable and contextual values ​​within the current query or stored procedure. However, if you need to retrieve an identity value in a different scope or session, IDENT_CURRENT(name) provides that functionality.

The above is the detailed content of What are the Differences Between `@@IDENTITY`, `SCOPE_IDENTITY()`, `IDENT_CURRENT()`, and `IDENTITY()` in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!