Difference Between IDENT_CURRENT(), @@IDENTITY AND SCOPE_IDENTITY()

 

IDENT_CURRENT(‘TABLENAME’)

This will return the last inserted identity of table.

SCOPE: This function is not limited to any “SCOPE OR SESSION”. But it is Table Specific.

See the Following Syntax.

Select IDENT_CURRENT(‘TableName’) – Parameter

Returns the Last Inserted Identity of Table (Whose Name Is Supplied As Parameter)

If Two Users Are Inserting Rows into Same Table and At Any Point Both The Users are Trying to find out Identity of table then both will get the same. i..e. Last Inserted Identity.

@@IDENTITY

This will return Identity Generated in any table within the Current Session.

SCOPE : Current Connection Or Current Session

Syntax

Select @@IDENTITY

Since there is no parameter is passed as we have In IDENT_CURRENT function. so its not table specific. This will return last inserted identity in any table in the current session

For Example If You have two tables TableA and TableB, Both the Table has Identity Column.

TableA has a Trigger which insert value into TableB.

Now If User Insert Data Into TableA

And User runs Select @@IDENTITY To find out last inserted Identity. So This will Return Identity From TableB not From TableA. As I Said @@IDENTITY is not limited to current execution scope. It is limited to current session.

SCOPE_IENTITY()

This will return Identity Generated in any table within the Execution Scope.

SCOPE : Execution Scope (Can Be Inside Store Procedure or Triggers)

Syntax

Select SCOPE_IENTITY()

For Example If You have two tables TableA and TableB, Both the Table has Identity Column.

TableA has a Trigger which insert value into TableB.

Now If User Insert Data Into TableA

And User runs Select SCOPE_IENTITY() To find out last inserted Identity. So This will Return Identity From TableA not From TableB. As SCOPE_IENTITY()  Limited to Current Execution Scope.