SQL SERVER – Difference Between SCOPE_IDENTITY(), @@IDENTITY and CURRENT_IDENT()

Scope_identity() will return the identity value generated with current scope in either a trigger or a stored procedure.

@@Identity will return the identity value generated with current session in any table

Ident_Current() will return the identity value generated with a particular table specified

Let us see the difference by using an example :

For this, let us first create two  tables TableA and TableB

Create Table TableA(ID INT IDENTITY(1,1) PRIMARY KEY , NAME VARCHAR(25) NOT NULL)

Create Table TableB(ID INT IDENTITY(100,5) PRIMARY KEY , NAME VARCHAR(25) NOT NULL)

TableA is having an Identity column ID with starting value 1 and increment value 1

TableB is having an Identity column ID with starting value 100 and increment value 5

Now Inserting Records into these tables,

Insert into TableA values('AAA')
Insert into TableA values('BBB')
Insert into TableA values('CCC')

Insert into TableB values('AAA')
Insert into TableB values('BBB')
Insert into TableB values('CCC')

On Retrieving the values from TableA and TableB,

Select * From TableA

Select * From TableB

Now let us create a Trigger on TableA, Which inserts a new record into the Table TableB.

Create Trigger Trigger_A on TableA for Insert as
Declare @Name varchar(25)
Begin
     Select @Name =  Name from Inserted
     Insert into TableB Values(@Name)
End

Now let us insert a new record in TableA,

Insert into TableA values('DDD')

Now let us have a look at our tables, TableA and TableB

Select * From TableA

Select * From TableB

Now On checking the values of Scope_identity() , @@Identity

Select  Scope_identity() ScopeIdentity, @@Identity IdentityValue

Here Scope_identity()  is the identity value that is generated with in the current scope -  the trigger,  which is 4.

@@ Identity is the last identity value that is generated with in the current session, i.e. in this case 115.

Also, Ident_Current() is pertaining to a specific table.

Select Ident_Current('TableA') Last_identity

 
Select Ident_Current('TableB') Last_identity