Unique Identifiers: find the last one inserted

SCOPE_IDENTITY vs @@IDENTITY

Date Revised: August 14 2009

Determining the 'identity' of the last record inserted into a table with t-sql is easy; you can use SCOPE_IDENTITY or @@IDENTITY. Some folks know about @@IDENTITY but alot of folks don't know about SCOPE_IDENTITY or understand the difference between the two. This article will demonstate that difference.

SCOPE_IDENTITY will return the identity of the last inserted record but only from within the scope of the insert statement. In other words, if I call an INSERT statement, the identity of the last record inserted by that statement will be returned.

@@IDENTITY is different. You need to be carefull with this one. Some folks use it thinking the identity of the INSERT statement they call will be returned and in most cases it will, but, what if the table you are inserting into has a trigger that inserts into another table? Yep, you guessed it, you will get back the identity of the record inserted by the trigger.

USE TempDB

GO

IF OBJECT_ID('Table1') IS NOT NULL DROP TABLE Table1;

IF OBJECT_ID('Table2') IS NOT NULL DROP TABLE Table2;

-- create two temp tables for demo

-- note that I'm using differnt identity values to allow

-- user to see differences

CREATE TABLE Table1

(id INT IDENTITY(1,1)PRIMARY KEY, lastname VARCHAR(15)NOT NULL)

INSERT Table1 VALUES ('Smith');

INSERT Table1 VALUES ('Jones');

GO

CREATE TABLE Table2

(id INT IDENTITY(10,1)PRIMARY KEY, city VARCHAR(15)NOT NULL);

INSERT Table2 VALUES ('Berlin');

INSERT Table2 VALUES ('New York');

GO

-- create a trigger on Table1 that will insert a record into

-- Table2 on insert.

CREATE TRIGGER Table1_Insert_Trigger

ON Table1

FOR INSERT AS

BEGIN

    INSERT Table2 VALUES ('Bangor');

END

GO

-- inserting record into Table1 fires trigger.

INSERT Table1 VALUES ('Doe');

GO

SELECT * FROM Table1;

SELECT * FROM Table2;

-- This will return 3

-- It's within the current scope meaning the value that I inserted,

-- in this case 'Doe' into Table1

SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY];

-- This will return 12

-- because a trigger fired after I inserted 'Doe' into Table1

-- that inserted a new record into Table2, the identity for

-- Table2 is returned because it was the last record to insert.

SELECT @@IDENTITY AS [@@IDENTITY];

GO

Running the above code will give the following results: