Best way to get identity of inserted row?

@@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

http://msdn.microsoft.com/en-us/library/ms175098.aspx

Advertisements

3 thoughts on “Best way to get identity of inserted row?

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

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s