SCOPE_IDENTITY() or @@IDENTITY

,

I've seen a lot of usage of both functions in the T-SQL codes. However, their usage sometimes matters. The expectation from @@IDENTITY can sometimes be not 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
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. 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.

Rate

Share

Share

Rate