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.
