A very common question during the interview is to provide difference between IDENT_CURRENT, @@IDENTITY and SCOPE_IDENTITY(). All are used to retrieve the value of identity column after DML statement execution. All three functions return last-generated identity values. However, the scope and session on which last is defined in each of these functions differ:
- IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope.
- @@IDENTITY returns the last identity value generated for any table in the current session, across all scopes.
- SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.
The IDENT_CURRENT function returns NULL when the function is invoked on an empty table or on a table that has no identity column.
I found a good article and an example which explains the difference very effectively. Here is the link for that.
Happy Programming !!!