SCOPE_IDENTITY (Transact-SQL)
Returns the last identity value inserted
into an identity column in the same scope. A scope is a module: a stored
procedure, trigger, function, or batch. Therefore, two statements are in the
same scope if they are in the same stored procedure, function, or
batch.
Examples
The following example creates two
tables, TZ
and TY, and an INSERT trigger on TZ. When a row is inserted to table
TZ, the trigger (Ztrig) fires and inserts a row in
TY.
USE tempdb GO
CREATE TABLE TZ ( Z_id int
IDENTITY(1,1)PRIMARY KEY, Z_name varchar(20) NOT NULL)
INSERT TZ VALUES ('Lisa')
INSERT TZ VALUES ('Mike')
INSERT TZ VALUES ('Carla')
SELECT * FROM TZ
--Result set: This is how table TZ
looks.
Z_id Z_name
-------------
1 Lisa
2 Mike
3 Carla
CREATE TABLE TY ( Y_id int
IDENTITY(100,5)PRIMARY KEY, Y_name varchar(20) NULL)
INSERT TY (Y_name) VALUES
('boathouse')
INSERT TY (Y_name) VALUES
('rocks')
INSERT TY (Y_name) VALUES
('elevator')
SELECT * FROM TY
--Result set: This is how TY
looks:
Y_id Y_name
---------------
100 boathouse
105 rocks
110 elevator
/*Create the trigger that inserts a row
in table TY when a row is inserted in table TZ*/
CREATE TRIGGER Ztrig ON TZ FOR
INSERT
AS
BEGIN
INSERT TY VALUES
('')
END
/*FIRE the trigger and determine what
identity values you obtain with the @@IDENTITY and SCOPE_IDENTITY
functions.*/
INSERT TZ VALUES ('Rosalie') SELECT
SCOPE_IDENTITY() AS [SCOPE_IDENTITY]
GO
SELECT @@IDENTITY AS
[@@IDENTITY]
GO
Here is the result set.
SCOPE_IDENTITY 4
/*SCOPE_IDENTITY returned the last
identity value in the same scope. This was the insert on table
TZ.*/
@@IDENTITY 115
/*@@IDENTITY returned the last identity
value inserted to TY by the trigger. This fired because of an earlier insert on
TZ.*/
B. Using @@IDENTITY and SCOPE_IDENTITY()
with replication
The following examples shows how to use
@@IDENTITY and SCOPE_IDENTITY() for inserts in a database that is published for
merge replication. Both tables in the examples are in the AdventureWorks sample database: Person.ContactType is not published, and
Sales.Customer
is published. Merge
replication adds triggers to tables that are published. Therefore,
@@IDENTITY
can return the value from
the insert into a replication system table instead of the insert into a user
table.
The Person.ContactType table has a maximum identity value of
20. If you insert a row into the table, @@IDENTITY and SCOPE_IDENTITY() return the same value.
USE AdventureWorks; GO INSERT INTO
Person.ContactType ([Name]) VALUES ('Assistant to the Manager') GO SELECT
SCOPE_IDENTITY() AS [SCOPE_IDENTITY] GO SELECT @@IDENTITY AS [@@IDENTITY]
GO
Here is the result set.
SCOPE_IDENTITY 21 @@IDENTITY
21
The Sales.Customer table has a maximum identity value of
29483. If you insert a row into the table, @@IDENTITY and SCOPE_IDENTITY() return different values.
SCOPE_IDENTITY()
returns the value from the
insert into the user table, whereas @@IDENTITY returns the value from the insert into
the replication system table. Use SCOPE_IDENTITY() for applications that require access to
the inserted identity value.
INSERT INTO Sales.Customer
([TerritoryID],[CustomerType]) VALUES (8,'S') GO SELECT SCOPE_IDENTITY() AS
[SCOPE_IDENTITY] GO SELECT @@IDENTITY AS [@@IDENTITY] GO
Here is the result set.
SCOPE_IDENTITY 24984 @@IDENTITY
89