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’)