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.

 

Published Sunday, September 18, 2005 2:12 PM by Jackie Goldstein
Filed under: , , ,

Comments

Sunday, September 18, 2005 8:53 AM by Frans Bouma

# re: SCOPE_IDENTITY() versus @@Identity

though with an insert trigger on a view, you need @@IDENTITY, as SCOPE_IDENTITY() won't work.
Monday, September 19, 2005 9:53 AM by Luciano Evaristo Guerche

# re: SCOPE_IDENTITY() versus @@Identity

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.
Monday, September 19, 2005 3:30 PM by Charles Chen

# re: SCOPE_IDENTITY() versus @@Identity

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.
Monday, September 19, 2005 5:20 PM by Jackie Goldstein

# re: SCOPE_IDENTITY() versus @@Identity

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
Monday, August 21, 2006 12:26 PM by Dave Nickson

# re: SCOPE_IDENTITY() versus @@Identity

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.

Tuesday, October 24, 2006 8:54 AM by Insight Knowledge Base » Get identity seed after insert

# Insight Knowledge Base » Get identity seed after insert

PingBack from http://www.nathanbedford.com/kb/insight/?p=3