In response to several recommendations by folks in the newsgroups to use @@Identity to fetch the last-set identity value...
Experts: Please don't suggest @@Identity to anyone without understanding and explaining the implications of this advice. While JET is stuck with @@Identity, it's relatively safe, but the continued use of @@Identity with SQL Server is questionable at best. While there are some special circumstances where SCOPE_IDENTITY() isn't the best approach, it should be recommended as a "first choice". As you know, if a trigger fires or other code executes that changes a row in any table, the @@Identity value will change and return a bogus value with potentially disasterous effect. This might not be a factor for simple systems, but as developers make their applications more sophisticated, they might inadvertently add a trigger or other code that will alter the behavior of any number of stored procedures.
Rather than "bogus value", I'd say "unexpected value". I guess it's a matter of semantics, but bogus implies to me that the value has no validity at all, which isn't true. More directly, I'd say it will return the value of the most recently created identity value in the SQL Server session, which may or may not be the identity value of the table about which you are concerned.
Agreed, @@IDENTITY should NOT be suggested as a blanket solution for SQL Server.
This is a prety interesting topic as I find myself using @@identity ALL THE TIME !!!
If I understand well, what you mean is that if you use @@identity after inserting a row in a table (let say the new indentity created has the value 50) but a trigger as fired for that table, the trigger might create an other row in an other table which might generate an other identity value let say 480.
Which would mean that select @@identity would return 480 instead of 50?
If so how should i do to retrieve the correct identity? Or should i never used identity fields?
Thank you,
Francois
Ah, no. In SQL Server (and this only applies to SQL Server), we recommend that you use SCOPE_IDENTITY() to return the latest (in scope) Identity value.
<quote>
While there are some special circumstances where SCOPE_IDENTITY() isn't the best approach
</quote>
What are these special circumstances? Can you please elaborate. Thanks!
We are using SCOPE_IDENTITY and we are still getting these unexpected results. The scenario is as follows: Insert into Table A (which has an identity column) which fires a trigger adding a row to Table B which has an identity column. On subsequent inserts or updates the identity from Table B is used instead of the identity from Table A. This is happening in an application written in VB 6 using SQL Server 2000. Any suggestions?
Not without a careful loook at the code... It does sound like @@Identity is being referenced (it should not be).