SCOPE_IDENTITY vs @@IDENTITY vs IDENT_CURRENT()

 SCOPE_IDENTITY()
It returns the last IDENTITY value produced on a connection and by a statement in the same scope, regardless of the table that produced the value.
SCOPE_IDENTITY(), like @@IDENTITY, will return the last identity value created in the current session

  @@IDENTITY
It returns the last IDENTITY value produced on a connection, regardless of the table that produced the value, and regardless of the scope of the statement that produced the value.
@@IDENTITY will return the last identity value entered into a table in your current session. While @@IDENTITY is limited to the current session, it is not limited to the current scope. If you have a trigger on a table that causes an identity to be created in another table, you will get the identity that was created last, even if it was the trigger that created it.

 IDENT_CURRENT(‘tablename’)
It returns the last IDENTITY value produced in a table, regardless of the connection that created the

NOTE

To avoid the potential problems associated with adding a trigger later on, always use SCOPE_IDENTITY() to return the identity of the recently added row in your T SQL Statement or Stored Procedure.

Identity

 Identity is a state or fact which distinguishes  one object  from another objects  , so identity column is one of the way of make rows  distinguishable in a table .

Decalring

                         IDENTITY  ( seed , increment ) 

Seed  : Is the value that is used for the very first row loaded into the table.

Increment : Is the incremental value that is added to the identity value of the previous row that was loaded.

CREATE TABLE employees

(

 id_num int IDENTITY(1,1),

 fname varchar (20),

 minit char(1),

 lname varchar(30)

)

DATA Insertion on Table

Sql server automatically takes care of incrementing and inserting this column value.

We can forcefully insert identity by running following

        SET IDENTITY_INSERT tablename off  

Important point

   Next identity column value will be the next value of the max value inserted on the column (if the value exists or not in the table)

 Ie