@@IDENTITY returns the last identity value generated for any table in the current session, across all scopes. You need to be careful here, since it’s across scopes. You could get a value from a trigger, instead of your current statement.
Another way said: @@IDENTITY returns the id of the last thing that was inserted by your client’s connection to the database.
Most of the time this works fine, but sometimes a trigger will go and insert a new row that you don’t know about, and you’ll get the ID from this new row, instead of the one you want
SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope. Generally what you want to use.
Another way said: SCOPE_IDENTITY() solves this problem. It returns the id of the last thing that you inserted in the SQL code you sent to the database. If triggers go and create extra rows, they won’t cause the wrong value to get returned.
IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope. This lets you specify which table you want the value from, in case the two above aren’t quite what you need (very rare). You could use this if you want to get the current IDENTITY value for a table that you have not inserted a record into.
Also said: IDENT_CURRENT returns the last ID that was inserted by anyone. If some other app happens to insert another row at an unforunate time, you’ll get the ID of that row instead of your one.
If you want to play it safe, always use SCOPE_IDENTITY(). If you stick with @@IDENTITY and someone decides to add a trigger later on, all your code will break.
More information:
http://blog.beyondrelational.com/2009/03/sql-server-identity-columns.html
Jacob said,
March 11, 2009 at 3:56 pm
Nice article. Very compact, and straight to the point. Very useful.
Tony said,
May 12, 2009 at 9:22 pm
I was wondering if you might comment on another related situation capturing identities. I have a procedure that calls several nested procedures that all insert records with identities and those identities need to be captured and passed to secondary procedure calls for foreign key use. I thought about returning the identities instead of using @@identity in the primary (calling) procedure to be safe. Using scope_identity in the primary procedure returns null.
Here’s an example of my problem using two nested procedures:
1. execute proc a (primary procedure) which begins a transaction
2. proc a then executes proc b (nested procedure) which inserts a record
3.proc a then executes proc c (nested procedure) passing the identity issued in proc b
4.proc a then commits the transaction to complete the process
In summary, my concern is that @@identity may return a value from an unknown trigger if not careful and possibly I should be using scope_identity in the called procedures to set and return the identity back to the calling procedure.
Thank you for your time!
Tony
martin said,
July 28, 2009 at 3:26 pm
Yes, that’s right, always use SCOPE_IDENTITY() with parameter output, I’ve gone thru difficult thing using @@IDENTITY when trigger in place. Remember to re-test when you adding trigger and set replication.