Differance between @@IDENTITY ,SCOPE_IDENTITY() and DENT_CURRENT(‘tablename’)

It is very good question for interview to select this question, this is really very good question to ask.

SELECT @@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 last inserted id , based on current session not based on scope, if there will be any Trigger on the specified table it will return the last of the trigger , if trigger willl generate the id.

 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, but it is based on also scope, it is not dependent on any trigger of specified table , if trigger will create any last inserted id , it will not  return any id by trigger .

the main difference is it is not based on trigger.

IDENT_CURRENT(‘tablename’)

It is useful to get last id for specified table , not depending on connection and not scope based.It is not based on session also.

if i wanted to get last inserted id of table called registration, so

query will be like.

SELECT @Id=IDENT_CURRENT(‘registration’)