I was just reading the latest dotnetwire newsletter and this article caught my eye.

Synchronize Identity Values between Database and DataSet During Updates

After inserting the rows in the database your DataTable does not automatically reflect the identity values of as assigned by the database. The problem can be solved by the clever use of stored procedures and output parameters.

It gives a very brief description of an important idea, how to return identity values from an insert without having to do another complete select query (even if you batch it together). I see only one small problem with the sample (besides how little detail it covers) and it is a very common mistake; the use of @@IDENTITY to return the PK value from an INSERT.

Assuming you have SQL Server 2000 or later, I wouldn’t recommend using @@IDENTITY to return the PK of the last inserted record, I’d use SCOPE_IDENTITY( ) instead. @@IDENTITY returns the last inserted identity value, which isn’t necessarily the record you were just inserted. If a trigger, or multiple chained triggers, has fired in response to your insert it is possible you will retrieve a PK value from a completely different table. SCOPE_IDENTITY( ), on the other hand, returns the last identity value in the same scope, which is the Insert you just executed.

If you are looking for information on this topic, check out William Vaughn’s article on just this subject: Managing an @@IDENTITY Crisis.

The SQL docs on @@IDENTITY and SCOPE_IDENTITY() might also be useful.