Difference between @@IDENTITY, SCOPE_IDENTITY () and IDENT_CURRENT

In this article we are going to discuss difference between @@IDENTITY, SCOPE_IDENTITY () and IDENT_CURRENT.  These functions are used to get last inserted rows identity(primary key value) in the table. Let's discuss it in more detail.

@@IDENTITY
It will return last or newly inserted record id(primary key value) of table in current session but it’s not limited to current scope. After inserting a row if any trigger or functions runs & they inserted a new row in table then it will return that latest inserted record id by trigger or function.

Note: Use this property whenever we don’t have any other functions or triggers.

Syntax:

SELECT @@IDENTITY

Example:

          Declare @UserId int =0
          INSERT  INTO [dbo].[Users]
                        ( [UserName] ,
                          [FirstName] ,
                          [LastName] ,
                          [Email]                         
                        )
                VALUES  ( 'shivam' ,
                          'shivam' ,
                          'srivastava' ,
                          'foo@bar.com' 
                        )

          --Any external function or procedure may not call after insert.
          SET @UserId = @@IDENTITY() 

After insert record in user table it will return last inserted UserId in Users table.

SCOPE_IDENTITY()
It will return a newly inserted record identity (Primary Key value) of the table in the current session and it’s limited to the current scope. It means it will return the identity value of current inserted row. Any external function & procedure will not affect it.

Note:I recommend this property to get the identity value of current inserted row. Since no external function & procedure will not affect it.

Syntax:

SELECT SCOPE_IDENTITY()

Example:

          Declare @UserId int =0
          INSERT  INTO [dbo].[Users]
                        ( [UserName] ,
                          [FirstName] ,
                          [LastName] ,
                          [Email]                         
                        )
                VALUES  ( 'shivam' ,
                          'shivam' ,
                          'srivastava' ,
                          'foo@bar.com' 
                        )

          --Any external function or procedure will not effect it.
          SET @UserId = SCOPE_IDENTITY()

IDENT_CURRENT
It will return the last record identity(Primary key value) of the table. It is not limited to, any session or scope.

Syntax:

SELECT IDENT_CURRENT('table-name')