SCOPE_IDENTITY was introduced by Microsoft in SQL Server 2000. This article will try to explain the difference between @@IDENTITY and SCOPE_IDENTITY() using a very simple example rather than complicated definitions.
So, let's create 2 tables:
CREATE TABLE Agenda
(
AgendaId INT IDENTITY(1,1) NOT NULL,
[Name] VARCHAR(30),
Surname VARCHAR(30)
)
Please note that identity start at 1.
CREATE TABLE DbLog
(
DblogId INT IDENTITY(10,1) NOT NULL,
LogText TEXT
)
Please note that identity start at 10.
Next, let's create a trigger for insert into Agenda table. It will simply log the entry into DbLog table.
CREATE TRIGGER dbo.AgendaLog ON Agenda AFTER INSERT
AS
BEGIN
SET NOCOUNT ON
DECLARE @Name VARCHAR(30)
DECLARE @Surname VARCHAR(30)
SELECT @Name=[Name], @Surname=Surname FROM inserted
INSERT INTO DbLog (LogText)
VALUES ('Agenda item: Name=' + ISNULL(@Name, '') + ' Surname=' + ISNULL(@Surname, ''))
END
GO
And finally, a simple stored procedure that will insert a record into Agenda table:
CREATE PROCEDURE dbo.NewAgendaEntry
(@Name VARCHAR(30), @Surname VARCHAR(30))
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO Agenda (Name, Surname)
VALUES (@Name, @Surname)
PRINT 'IDENTITY VALUE=' + CAST(@@IDENTITY AS VARCHAR)
PRINT 'SCOPE_IDENTITY VALUE=' + CAST(SCOPE_IDENTITY() AS VARCHAR)
END
GO
Let's run the stored procedure:
EXEC NewAgendaEntry 'John', 'Doe'
Result: IDENTITY VALUE=10; SCOPE_IDENTITY VALUE=1
Difference can be explained like this:
@@IDENTITY - returns the most recently created identity (in our case for DbLog table)
SCOPE_IDENTITY() - will return the last identity value that you explicitly created
Recommended is to always use SCOPE_IDENTITY() to return the identity of the recently added row.