Understanding Identity Values in SQL Server

What is the Difference Between @@IDENTITY and SCOPE_IDENTITY()?

The fundamental difference between @@IDENTITY and SCOPE_IDENTITY() lies in their scope when retrieving the last-generated identity value. While both return the last identity values generated in any table within the current session, SCOPE_IDENTITY() returns values inserted only within the current scope, whereas @@IDENTITY is not limited to a specific scope and can reflect identity values generated by triggers or functions.

In SQL Server, an identity column is a special type of column that automatically generates sequential numeric values for new records. This is commonly used for primary keys to ensure unique identification for each row. When a new row is inserted into a table with an identity column, a new identity value is automatically assigned. Retrieving this generated value is often crucial for subsequent operations, such as inserting related data into other tables.

Two primary functions allow you to retrieve these last-generated identity values: @@IDENTITY and SCOPE_IDENTITY(). Understanding their differences is key to preventing unexpected behavior, especially in complex database operations involving triggers.

Key Differences: @@IDENTITY vs. SCOPE_IDENTITY()

The most critical distinction, as highlighted in the provided reference, is the concept of "scope":

• SCOPE_IDENTITY(): This function returns the last identity value generated within the current scope of the batch, stored procedure, or function. This means if a trigger fires as a result of your INSERT statement and that trigger inserts into another table with an identity column, SCOPE_IDENTITY() will still return the identity value from your original INSERT statement, not the one generated by the trigger.
• @@IDENTITY: This global variable returns the last identity value generated in any table in the current session, regardless of the scope. If an INSERT statement causes a trigger to fire, and that trigger inserts a row into another table with an identity column, @@IDENTITY will return the identity value generated by the trigger's INSERT, not necessarily the one from your initial statement.

Comparative Table

Feature	@@IDENTITY	SCOPE_IDENTITY()
Scope Limitation	Not limited to a specific scope	Limited to the current scope (batch, stored procedure, function)
Trigger Behavior	Returns the last identity value generated by any INSERT in the session, including those by triggers.	Returns the last identity value generated by the INSERT statement within the current execution scope, ignoring trigger-generated identities.
Use Case	Less commonly recommended for general use due to potential ambiguity with triggers.	Preferred for reliability when you need the identity value from your specific INSERT statement.
Return Type	numeric(38,0)	numeric(38,0)

Practical Implications and Examples

Consider a scenario where you insert a record into TableA, and TableA has an AFTER INSERT trigger that subsequently inserts a record into TableB. Both TableA and TableB have identity columns.

Example Scenario:

-- Create sample tables
CREATE TABLE TableA (
    TableAID INT IDENTITY(1,1) PRIMARY KEY,
    Name NVARCHAR(100)
);

CREATE TABLE TableB (
    TableBID INT IDENTITY(1,1) PRIMARY KEY,
    Description NVARCHAR(100),
    TableAID_FK INT
);

-- Create a trigger on TableA
CREATE TRIGGER trg_InsertIntoTableB
ON TableA
AFTER INSERT
AS
BEGIN
    INSERT INTO TableB (Description, TableAID_FK)
    SELECT 'Triggered insert for ' + i.Name, i.TableAID
    FROM inserted i;
END;

-- Perform an insert operation
INSERT INTO TableA (Name) VALUES ('Item One');

-- Retrieve identity values
SELECT
    @@IDENTITY AS GlobalIdentity,
    SCOPE_IDENTITY() AS ScopeIdentity;

-- Clean up
DROP TRIGGER trg_InsertIntoTableB;
DROP TABLE TableB;
DROP TABLE TableA;

Result of the SELECT statement after INSERT INTO TableA:

GlobalIdentity	ScopeIdentity
[Identity_Value_from_TableB]	[Identity_Value_from_TableA]

In this example:

• SCOPE_IDENTITY() would return the TableAID from the INSERT into TableA (e.g., 1).
• @@IDENTITY would return the TableBID from the INSERT into TableB (e.g., 1) because the trigger's insert was the last identity generated in the session.

When to Use Which

• Always prefer SCOPE_IDENTITY(): For most applications, SCOPE_IDENTITY() is the recommended function because it provides the identity value for the exact statement you executed, isolating you from side effects like triggers that might generate their own identity values. This predictability is crucial for data integrity and application logic.
• Use @@IDENTITY with Caution: @@IDENTITY can be useful in very specific, niche scenarios where you genuinely need the absolute last identity generated in the session, regardless of scope. However, its use often leads to subtle bugs that are hard to diagnose, especially in environments with complex trigger logic or third-party components.

By understanding the distinction between @@IDENTITY and SCOPE_IDENTITY(), developers can write more robust and predictable database applications.