Retrieving Identity Values

Q: How do you retrieve the Identity value of a row that was last inserted in a SQL Server DB?

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