Are you still using @@IDENTITY?

  • Article

Are you still using @@IDENTITY?  It seems many people are.  If you are, are you aware that it might not always return what you expect?  No? then read on.  @@IDENTITY will return the last IDENTITY column value inserted across any scope in the current session.  This means that if a trigger, or similar, were to insert a row in another table (T2), other than the one you're interested in (T1), inside the same transaction and after you insert into T1, you will get the Identity value of T2 instead of T1.

SCOPE_IDENTITY returns values inserted only within the current scope and obviates this problem.  You may also be interested in IDENT_CURRENT as this is not limited by scope and session; it is limited to a specified table.

For more info and an example, check these functions out in Books On Line.

Comments

  • Anonymous
    January 01, 2003
    Found this simple but overlooked database thought as I was running through blogs this morning. We all use @@IDENTITY as database developers to get the last IDENTITY column value during the current session. I have to admit that I do use this variable in MSSQL. Well the truth is that if you use triggers that could add records to tables...