Friday, June 23, 2006

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

No comments: