SCOPE_IDENTITY() or @@IDENTITY

I’ve seen quite a lot of usage of both functions in the T-SQL codes. However, their usage sometimes matters. The expectation from @@IDENTITY can sometimes not be the desired.

This post shows why you should use SCOPE_IDENTITY() instead of @@IDENTITY when using it with tables that have triggers.

CREATE DATABASE IdentDB;
GO
USE IdentDB;
GO
CREATE TABLE Contacts(
    ID int IDENTITY(1, 1), FirstName nvarchar(50), LastName nvarchar(50)
);
 
INSERT INTO Contacts(FirstName,LastName)
VALUES ('Igor','Micev');

Select the SCOPE_IDENTITY() AND @@IDENTITY functions’ current values.

SELECT SCOPE_IDENTITY() [SCOPE_IDENTITY], @@IDENTITY [@@IDENTITY]
Output:
SCOPE_IDENTITY @@IDENTITY
-------------- ------------
1               1

The table Contacts doesn’t have a trigger and the values of both the functions are identical.

Create Contacts history table and a trigger on Contacts.

CREATE TABLE Contacts_Hist(
HistID int IDENTITY(1, 1), ID int, 
FirstName nvarchar(50), 
LastName nvarchar(50)
);
 
--Create an insert trigger on Contacts
CREATE TRIGGER TRG_Const_Hist ON dbo.Contacts
FOR INSERT
AS
BEGIN
       SET NOCOUNT ON;
       INSERT INTO dbo.Contacts_Hist( ID, FirstName, LastName )
                 SELECT ID, FirstName, LastName
                 FROM INSERTED;
END;

Insert a new record.

INSERT INTO Contacts(FirstName,LastName)
VALUES ('Branko','Nikolov')

SELECT * FROM Contacts
Output:
ID          FirstName           LastName
----------- ------------------  ---------------
1           Igor                Micev
2           Branko              Nikolov
 
SELECT SCOPE_IDENTITY() [SCOPE_IDENTITY], @@IDENTITY [@@IDENTITY]
Output:
SCOPE_IDENTITY                          @@IDENTITY
------------------                      -----------
2                                       1

Now the values of the functions differ. SCOPE_IDENTITY() returns a value of 2 which is the desired, and @@IDENTITY returns a value of 1 which is not the desired. That’s because of the trigger, and the usage of @@IDENTITY produces a bug in such situations.

Now you know why you should use SCOPE_IDENTITY() in your T-SQL codes.