Mat Stephen's SQL Server WebLog

All things SQL Server. Complied by Matthew Stephen - SQL Server Product Specialist, Microsoft UK

Are you still using @@IDENTITY?

Are you still using @@IDENTITY?

  • Comments 3
  • Likes

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

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment