Attention: We are retiring the ASP.NET Community Blogs. Learn more >

SCOPE_IDENTITY() versus @@Identity

Bill Vaughn points out that we should normally NOT be using @@Identity to fetch the last-set identity value with SQL Server.  This is definitely an important, and often misunderstood (and misused) point.

I want to note that the code generated (for typed DataSets) by the latest builds of VS 2005 (post Beta2) are now generating code with SCOPE_IDENTITY() rather than @@Identity.

 

5 Comments

  • though with an insert trigger on a view, you need @@IDENTITY, as SCOPE_IDENTITY() won't work.

  • Do you know if code generated (for typed datasets) uses CHECKSUM, BINARY_CHECKSUM AND CHECKSUM_AGG for optimistic locking control?



    Like SCOPE_IDENTITY, these SQL Server functions are VALUABLE, but not used by ADO and ADO.NET, at least in VB6, VB.Net 2002 and VB.Net 2003 wizards.

  • I'd like to add a note to this.



    When SELECTing SCOPE_IDENTITY(), *do not* try to select it from a table. What ends up happening is that you will get the IDENTITY value back once for each row in the table.



    At one company that I worked for, this was causing a performance issue that was quite tricky to track down because of the fact that the SELECT looks normal otherwise.

  • Luciano,



    No - the DataSet tools only offer 2 options for implementing optimistic concurrency:

    (1) If the table has a timestamp field, it will use that

    (2) If there is no timestamp field, it will compare all of teh fields.



    Jackie

  • I came across this problem myself some time ago and there is a really important difference between the two. I've seen numerous bits of code using @@Identity instead of SCOPE_IDENTITY(). @@Identity is not thread safe.

Comments have been disabled for this content.