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

Comments

Popular posts from this blog

Working with double quotes (") in VB.Net

Using VB Script to display part of an IP address

Object reference not set to an instance of an object when using HTMLEditorExtender