Short but sweet little example from today's .NET Wire

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.

Comments

• Anonymous
  January 20, 2004
  what about using (on sql 2000):
  SELECT IDENT_CURRENT('tableName')
• Anonymous
  January 20, 2004
  If another user inserts to the table after you inserted and before you got IDENT_CURRENT, you will get the other user's new ident. Scope_identity() returns the latest one in your own scope, not globally like ident_current.
• Anonymous
  January 20, 2004
  Good to know, but according to the documentation, what constitutes a "session" in the context of a Web application?
• Anonymous
  January 20, 2004
  Personally I use stored procedures, open the connection right before calling a proc and close it right after (using connection pooling), so each procedure call is one session.
• Anonymous
  January 20, 2004
  Duncan
• Anonymous
  January 20, 2004
  The comment has been removed
• Anonymous
  January 20, 2004
  You are correct that @@IDENTITY works, however Duncan pointed out that @@IDENTITY returns the last identity inserted, but that identity might not be what you expect.
  
  If you have a trigger on a table you are inserting into (using:
  INSERT INTO tblMyTable ....
  SELECT @@IDENTITY
  ), and that trigger inserts into a different table (say tblMyTableLog - I've seen DBAs do this many times without telling me) which has an IDENTITY column, your call to @@IDENTITY will return the 2nd identity and not the first.
  
  The function Scope_Identity() returns the last IDENTITY value in the current session (so just your sproc, it ignores the triggers).
• Anonymous
  January 20, 2004
  SCOPE_IDENTITY() doesn't work on sqlserver7
  
  If you have no triggers in your database, @@IDENTITY is safe to use, as it is scoped within a connection.
  
  The real problem is in the IDENTITY concept itself. It's nice to have a mechanism which constructs unique values, but it's better to use natural key values for PK values instead of Identity columns. This will for example save you from misery in replication between 2 databases which both accept inserts in identity keyed tables. People should think first of natural keys before going for identity columns for PK values, most of the time there is a unique attribute in the table which can function as a PK value perfectly.
• Anonymous
  January 21, 2004
  More on this topic at URL
  http://ipattern.com/simpleblog/PermLink.aspx?entryid=25
• Anonymous
  January 21, 2004
  what about oracle database? what's the work around for database other than access and sql server 2000?
• Anonymous
  February 02, 2004
  I'm not the database guy Frans is, but it seems to me that you'll get better perf on int or guid indexes than varchar (a likely choice for natural keys). It also seems that you'll have an eaiser time extending your schema at a later time if you stick with identity columns (ints or guids).
• Anonymous
  February 03, 2004
  Thanks for the idea on SCOPE_IDENTITY (I didn't know about it).
  How about the case where you have a trigger which inserts another row into the same table? Any quick solutions for that one ?
• Anonymous
  April 04, 2004
  I use the @@IDENTITY to insert a new row in one table. Sometimes, looks like the table miss some row for prim ID. For example, it has 1, 2, 4, 5….. The 3 is missing. I don’t know what happy. AS we see there is a gap in the series. Is any way to stop it? We have more than one users at some time to enter the data.