IDENT_CURRENT and SCOPE_IDENTITY

IDENT_CURRENT is new built in function introduced in SQl 2005. By looking at the function name it looks very similar to SCOPE_IDENTITY.

I had one of the sp which was using IDENT_CURRENT and it was all working fine till I came across a problem which was reported last week. The sp which was returning Identity value had returned same idenity value for 2 sessions which occurred at the same time. Yes. the IDENT_CURRENT considers only last identity for the specific table in any session and any scope.

So if you have stored procedure running different sessions which is returning IDENT_CURRENT, there is a chance that you might get the latest inserted identity value from another session.

Here is the exact definition and difference between SCOPE_IDENTITY, IDENT_CURRENT, @@IDENTITY

•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.

So be careful when you use above functions. If you need a identity of the current scope in current session, you should use SCOPE_IDENTITY. If you need latest identity of a specified table no matter what is the session then you should use the IDENT_CURRENT