@@Identity returns the last auto incremented or identity value for any table in the current session over all scopes
. means that Its a GLOBAL identity value...
Example for explaining ALL SCOPES meaning...
Suppose that you have a table called Table1 and one INSERT trigger on Table1. The trigger logic is to insert same record into another table called Table2.
Script for above scenario is as follows:
CREATE TABLE Table1 ( ID int identity( 1,1), Name varchar(100))
CREATE TABLE Table2 ( ID int identity( 10, 1) Name VARCHAR(100))
CREATE TRIGGER Tri_InsertTrigger
INSERT Table2 SELECT name from inserted
If we insert one record into Table1, then what will be the IDENTITY value now ?
INSERT Table1 VALUES( 'Bandi')
SELECT @@IDENTITY [IdentityValue?]
SELECT ID Table1ID FROM Table1
It is very clear that the last identity value of Table1 is 1
. but @@IDENTITY returned 10
as identity value. means that the 10 is the identity value of last INSERT statement ( i.e. Table2's INSERT statement in TRIGGER)...
@@IDENTITY is global system variable, which returns IDENTITY value across all scopes( here the first table Table1, the trigger Tri_InsertTrigger and Table2).