Ok, these 3 functions could be confusing - I have seen people use these functions in-appropriately, so I decided to put together a basic list of differences between them. These are points that should be noted before using any of these SQL functions included in the title.
- These functions are very similar and should be used carefully
- If no inserts occur @@IDENTITY will be NULL
- If multiple rows are inserted, @@IDENTITY will hold the last identity value generated
- @@IDENTITY does not revert to the previous setting if the insert/select into statement fails - same applies if the transaction is rolled back.
- @@IDENTITY and SCOPE_IDENTITY will return identities generated in any table in the current session, however, SCOPE_IDENTITY will return the identity generated with the current scope - @@IDENTITY is not limited to scope but to the session.
- IDENT_CURRENT is not locked to any session or scope but to a table. It returns the identity generated for a specific table in any session or scope.
- @@IDENTITY does not flow across linked or remote servers.
Therefore, @@IDENTITY is not a reliable indicator of the most recent user-created identity - SCOPE_IDENTITY should be used.