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 (16)

  1. ron says:

    what about using (on sql 2000):


  2. dennis says:

    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.

  3. jeff says:

    Good to know, but according to the documentation, what constitutes a "session" in the context of a Web application?

  4. Dennis says:

    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.

  5. Duncan,

    @@IDENTITY is just fine to use. It does return the last Identity update for open connection.

    The issue arise when you use Dataset. There are two ways to do so:

    1. To execute insert;select @@IDENTITY in the same stored procedure

    2. OnRowUpdated event of the data adapter to execute SCOPE_IDENTITY( ) and update the Identity field in side of the dataset.

    I am currently working on the post that will cover this issue.

    I hope this helps, Maxim

    [ do you]

  6. adam says:

    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 ….


    ), 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).

  7. Frans Bouma says:

    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.

  8. Kevin says:

    what about oracle database? what’s the work around for database other than access and sql server 2000?

  9. Louis Parks says:

    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).

  10. Nullabee says:

    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 ?

  11. yhe says:

    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.