scope_identity vs ident_current

Question

After much research I am a little confused by which identity tracker I should use in sql.

From what I understand scope_identity will give me the last id updated from any table and ident_current will will return the last id from a specified table.

So given that information it would seem to me the best version to use (if you know which table you will be updating) is ident_current. Yet, upon reading it seems most people prefer to use scope_identity. What is the reasoning behind this and is there a flaw in my logic?

Solution 1

In that case you need to write the table name, what happens if you decide to change the table name? You then also must not forget to update your code to reflect that. I always use SCOPE_IDENTITY unless I need the ID from the insert that happens in a trigger then I will use @@IDENTITY

Also the bigger difference is that IDENT_CURRENT will give you the identity from another process that did the insert (in other words last generated identity value from any user) so if you do an insert and then someone does an insert before you do a SELECT IDENT_CURRENT you will get that other person's identity value

See also 6 Different Ways To Get The Current Identity Value which has some code explaining what happens when you put triggers on the table

Solution 2

From what I've read scope_identity() should be the right answer, however it looks like there is a bug in SQL 2005 and SQL 2008 that can come into play if your insert results in a parallel query plan.

Take a look at the following articles for more details:

@@IDENTITY vs SCOPE_IDENTITY() vs IDENT_CURRENT - Retrieve Last Inserted Identity of Record

Article: Six reasons you should be nervous about parallelism

See section titled: 1. #328811, "SCOPE_IDENTITY() sometimes returns incorrect value"

Comments

[dburges]

ident_current should NEVER be used to get the value you just inserted, it wil give you data integrity problems if you use it becasue it will give the last identity inserted even when it wasn't from your connection! If you have used this code anywhere in your database, you are at serious risk of having bad data.

[dburges]

Definitely a reason not to use paralleism.

[arkap]

Did I say anywhere that ident_current should be used everywhere?

[Priya Narayanan]

The KB article for this bug: support.microsoft.com/default.aspx?scid=kb;en-US;2019779