Use SCOPE_IDENTITY() instead of @@IDENTITY

Posted: August 11, 2010 in SQL Server 2005

This example shows why you should use SCOPE_IDENTITY() instead of @@IDENTITY.. The value from @@IDENTITY will return the value for the last inserted value which means that if there are triggers on a table, the value returned might NOT be what you are after. The value from SCOPE_IDENTITY() will return the value for the current insert only which is what you are after.

Copy and paste this piece of code into SQL Server 2005 which is just a proof of concept:

-- Create temp table 1
CREATE TABLE dbo.Test1 (col1 INT IDENTITY,
                        col2 VARCHAR(10),
                        col3 VARCHAR(10));

-- Insert initial values into #Test1
INSERT INTO dbo.Test1 (col2, col3) VALUES ('Rec1', 'Rec1.1');
INSERT INTO dbo.Test1 (col2, col3) VALUES ('Rec2', 'Rec2.1');
INSERT INTO dbo.Test1 (col2, col3) VALUES ('Rec3', 'Rec3.1');
INSERT INTO dbo.Test1 (col2, col3) VALUES ('Rec4', 'Rec4.1');
INSERT INTO dbo.Test1 (col2, col3) VALUES ('Rec5', 'Rec5.1');

-- Create temp table 2
CREATE TABLE dbo.Test2 (col1 INT IDENTITY,
                        col2 VARCHAR(10),
                        col3 VARCHAR(10));

-- Insert initial values into #Test2
INSERT INTO dbo.Test2 (col2, col3) VALUES ('Rec1', 'Rec1.1');
INSERT INTO dbo.Test2 (col2, col3) VALUES ('Rec2', 'Rec2.1');
INSERT INTO dbo.Test2 (col2, col3) VALUES ('Rec3', 'Rec3.1');
GO

-- Create trigger on #Test1 to insert record into #Test2
CREATE TRIGGER dbo.t_Test1 ON dbo.Test1
FOR INSERT AS
BEGIN
   INSERT INTO dbo.Test2 (col2, col3) VALUES ('Rec4', 'Rec4.1');
END;
GO

---- Declare variables
DECLARE @li_identity_value INT;
DECLARE @li_scope_identity_value INT;

-- Insert new record in #Test1
INSERT INTO dbo.Test1 (col2, col3) VALUES ('Rec1', 'Rec1.1');

-- Get the identity value for inserted record into #Test1
SET @li_identity_value = @@IDENTITY;
SET @li_scope_identity_value = SCOPE_IDENTITY();

-- Print the results
SELECT @li_identity_value AS identity_value_WRONG; -- You will give the WRONG value of 4 instead of 6.
SELECT @li_scope_identity_value AS scope_identity_value_CORRECT; -- This gives the CORRECT value of 6

---- Drop temp table
DROP TABLE dbo.Test1;
DROP TABLE dbo.Test2;
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s