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?

Answer 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

Answer 2

See this blogpost for your answer in detail. Scope_identity will never return identities due to inserts done by triggers. It wont be a great idea to use ident_current in a world of change where tablenames are changed..like in a dev env.

Answer 3

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

shortcut: http://tinyurl.com/4vbhju

(http://blog.sqlauthority.com/2007/03/25/sql-server-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"

shortcut: http://tinyurl.com/krm3q4

(http://sqlblog.com/blogs/aaron_bertrand/archive/2009/03/21/six-reasons-you-should-be-nervous-about-parallelism.aspx)