A: SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY can all be used to return values inserted into IDENTITY columns. However, each one has its own scope limitations. Scope refers to a specific module (i.e. a stored procedure, trigger, function, batch).
- SCOPE_IDENTITY will return last identity values generated in any table in the current session, but within the current scope.
- IDENT_CURRENT returns the value generated for a specific table in any session and any scope. It is not limited by scope and session, but it is limited to a specified table.
- @@IDENTITY will return the last IDENTITY column value inserted across any scope in the current session, but it is not limited to a specific scope.
For more information, see http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sa-ses_6n8p.asp