SQL-SCOPE_IDENTITY vs @@IDENTITY

SCOPE_IDENTITY and @@IDENTITY are both used in SQL Server to retrieve the last inserted identity value, but they differ in scope and context.

Here's a detailed comparison.

1. SCOPE_IDENTITY

• Scope - Returns the last identity value generated in the current session and current scope.
• Context Safety - It is limited to the current scope, which makes it safer in scenarios involving triggers or nested calls.
• Usage - Preferred when you want to ensure you're retrieving the identity value inserted by your current statement or procedure.

Example

INSERT INTO Employees (Name, Department) VALUES ('Rohatash Kumar', 'HR');
SELECT SCOPE_IDENTITY() AS LastIdentity;

This will return the identity value generated by the INSERT statement within the same scope.

2. @@IDENTITY

• Scope - Returns the last identity value generated in the current session and any scope.
• Context Safety - It includes values generated by triggers or other operations, which might lead to unexpected results.
• Usage - Use with caution, especially in environments with triggers that insert rows into other tables with identity columns.

Example

INSERT INTO Employees (Name, Department) VALUES ('Rohatash Kumar', 'IT');
SELECT @@IDENTITY AS LastIdentity;

This will return the last identity value generated by the INSERT statement or by any trigger that executed within the same session.

Here are practical examples to better understand the behavior of SCOPE_IDENTITY and @@IDENTITY, especially when triggers are involved.

Scenario

We have two tables, Employees and AuditLog, and a trigger that automatically logs an audit record whenever a new employee is added.

Table Structure

-- Employees table
CREATE TABLE Employees (
    EmployeeID INT IDENTITY(1,1) PRIMARY KEY,
    Name NVARCHAR(50),
    Department NVARCHAR(50)
);

-- AuditLog table
CREATE TABLE AuditLog (
    AuditID INT IDENTITY(100,1) PRIMARY KEY, -- Starts at 100
    LogMessage NVARCHAR(100)
);

Trigger on Employees Table

CREATE TRIGGER trg_AuditLog
ON Employees
AFTER INSERT
AS
BEGIN
    INSERT INTO AuditLog (LogMessage)
    SELECT CONCAT('Employee ', Name, ' added to department ', Department)
    FROM INSERTED;
END;

Case 1: Using SCOPE_IDENTITY

Query

INSERT INTO Employees (Name, Department)
VALUES ('Rohatash Kumar', 'HR');

SELECT SCOPE_IDENTITY() AS LastIdentity;

SCOPE_IDENTITY returns the identity value generated in the current scope, which is the EmployeeID from the Employees table.

Result

LastIdentity: 1  -- (Employees' EmployeeID)

Case 2: Using @@IDENTITY

Query

INSERT INTO Employees (Name, Department)
VALUES ('Rohatash Kumar', 'IT');

SELECT @@IDENTITY AS LastIdentity;

The trigger inserts a record into AuditLog after the employee is added to Employees. @@IDENTITY returns the last identity value generated in the session, regardless of the scope. This will be the AuditLog.AuditID because it is the last identity value created.

Result

LastIdentity: 100  -- (AuditLog's AuditID)

Difference between the SCOPE_IDENTITY vs @@IDENTITY