Be cautious about SCOPE_IDENTITY() where you are using ?

Remember that SCOPE_IDENTITY returns LAST IDENTITY value for ANY table in CURRENT SESSION.

means that if you have INSERTs of more than one tables, then what will be the SCOPE_IDENTITY value?

Tables creation

CREATE TABLE Table1 ( ID int identity( 1,1), Name varchar(100))
GO
CREATE TABLE Table2 ( ID int identity( 10, 1), Name VARCHAR(100))
GO

In another session, try to do the following inserts...

INSERT Table1 VALUES( 'First Insert statemnt in current session-Table1')
INSERT Table2 VALUES( 'LAST Insert statemnt in current session-Table2')
SELECT SCOPE_IDENTITY() ScopeIdentityValueinCurrentSession

Output:

ScopeIdentityValueinCurrentSession
10 

so it is very clear that the identity value from SCOPE_IDENTITY() varies based on the place where you are using SCOPE_IDENTITY()...

It will return the LAST identity value of JUST previous INSERT statement..

i.e. it is current session scope and also it is irrespective to table


If you insert 2nd record into both tables and using SCOPE_IDENTITY() function to know the each table's IDENTITY value, we can code like below...

INSERT Table1 VALUES( 'First Insert statemnt in current session-Table1')
SELECT SCOPE_IDENTITY() ScopeIdentityValueOfTable1 
INSERT Table2 VALUES( 'LAST Insert statemnt in current session-Table2')
SELECT SCOPE_IDENTITY() ScopeIdentityValueOfTable2 

Output:

ScopeIdentityValueOfTable1
2

ScopeIdentityValueOfTable2
11