Now I’m sure most developers and DBAs are aware of this, but I mention it because it crops up every once in a while in production code. There are 3 ways to retrieve the last generated identity value for table: @@IDENTITY, IDENT_CURRENT(‘tablename’) and SCOPE_IDENTITY().
If you want to make sure that retrieved identity value is correct, use only SCOPE_IDENTITY().
In fact, it’s worth doing a search for the other 2 possibilities across all your projects’ stored procedures just to be sure.
From Books Online:
- SCOPE_IDENTITY() returns the last identity value generated for any table in the current session and the current scope.
- IDENT_CURRENT(‘tablename) 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.