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

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

The four functions

SCOPE_IDENTITY(), IDENTITY(), @@IDENTITY and IDENT_CURRENT() are all used to retrieve auto-increment column values ​​in SQL Server, but their uses are different. Let’s dive into their differences.

SCOPE_IDENTITY()

SCOPE_IDENTITY()Retrieve the last inserted auto-increment column value in the current scope, which can be the current statement or batch. It does not consider auto-incrementing column values ​​generated in other scopes within the same session.

IDENTITY()

Contrary to the name, IDENTITY() is not used to retrieve auto-incrementing column values. It is used in SELECT...INTO statements to create auto-increment columns when deriving a new table from data in another table.

@@IDENTITY

@@IDENTITYRetrieves the last inserted auto-increment column value in the current session, regardless of its scope. This means that it includes auto-incrementing column values ​​generated in the current scope and other scopes during the same session.

IDENT_CURRENT()

IDENT_CURRENT() is a less used function designed to retrieve the last inserted auto-increment column value in a specific table or view, even across sessions.

When to use each function?

Generally, SCOPE_IDENTITY() is preferred because it provides isolation within the current scope. If you need to reference an auto-increment column value generated in a parent statement or stored procedure in the same scope, SCOPE_IDENTITY() ensures its accuracy.

@@IDENTITYUseful when accessing auto-increment column values ​​generated in other scopes within the session. For example, you can use it in a trigger or batch job when multiple operations occur in the same session.

IDENT_CURRENT() is mainly used in scenarios where you need to access auto-increment column values ​​in a specific table across multiple sessions, such as when connecting data from different data sources.

Example:

Consider the following code snippet:

<code class="language-sql">-- 向'Customers'表插入一条记录
INSERT INTO Customers (Name) VALUES ('John Doe');

-- 检索当前语句中生成的自增列值
SELECT SCOPE_IDENTITY();

-- 触发器触发并插入另一条记录
CREATE TRIGGER InsertAudit ON Customers
AFTER INSERT
AS
    INSERT INTO AuditLog (CustomerId, Action)
    VALUES (@@IDENTITY, 'INSERT');

-- 检索触发器生成的自增列值
SELECT @@IDENTITY;</code>

In this example, SCOPE_IDENTITY() will return the auto-increment column value of the record inserted into the "Customers" table, while @@IDENTITY will return the auto-increment column value created by the trigger after the audit log entry is inserted. This demonstrates how different scopes can lead to different results using @@IDENTITY.

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