Posted by Erland Sommarskog on 04/13/06 00:51

Tom Moreau (tom@dont.spam.me.cips.ca) writes:
> Don't use @@IDENTITY. You can have incorrect results if your INSERT
> fires a trigger which itself inserts into a table with an identity. Use
> SCOPE_IDENTITY().

Then again, there are cases where @@identity will give you the correct
result, and scope_identity() will not.

Now, I don't know how DAO works, but the suggestion to use scope_identity()
relies on the somewhat risky assumption that .AddNew performs a straight
insert. If DAO sets up a prepared query, run sp_executesql, or runs some
temporary stored procedure, scope_identity will not work. Since DAO is
a fairly old API, I would not expect it to be too sophisticated. Then
again, using scope_identity() means that you rely on the implementation
of something that could change with a service pack or a new release. (Not
that such are bloodly likely for DAO.)

Using @@identity is better, because it relies at least only on your
own application and schema which you have more control over.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]