SCOPE_IDENTITY usage in SQL Server

Bandi
Posted by Bandi under Sql Server category on | Points: 40 | Views : 241
SCOPE_IDENTITY() fucntion returns the last identity value for any table in current session. It retuns local identity value, NOT across all scopes.

Sample script for creating two tables Table1 ( IDENTITY starts with 1;) and Table2 ( Identity starts with 10 ).

CREATE TABLE Table1 ( ID int identity( 1 ,1), Name varchar(100))
GO
CREATE TABLE Table2 ( ID int identity( 10 , 1), Name VARCHAR(100))
GO
CREATE TRIGGER Tri_InsertTrigger
ON Table1
FOR INSERT
AS
INSERT Table2 SELECT name from inserted
GO

INSERT Table1 VALUES( 'Bandi')
SELECT SCOPE_IDENTITY() ScopeIdentityValue
SELECT ID Table1ID FROM Table1

ScopeIdentityValue 
1

Table1ID
1


Even though we have trigger on Table1, the SCOPE_IDENTITY() retuns current scope and local identity value for the table..

NOTE: If you wanted the last identity value of a table across ALL/global scopes, then use the @@IDENTITY property; otherwise use SCOPE_IDENTITY() for local identity value for any of table

Comments or Responses

Login to post response