Question: When to Use IDENT_CURRENT and SCOPE_IDENTITY()?
Answer: Earliest this week when I was co-hosting Group By online conference, I have heard this question. This reason, I believe I should discuss this question is because I realized that not many knows this interesting detail.
Before you start reading my answer, I suggest you read this blog post I wrote it earlier: SQL SERVER – @@IDENTITY vs SCOPE_IDENTITY() vs IDENT_CURRENT – Retrieve Last Inserted Identity of Record. When I wrote the blog post in year 2007, I had actually the same question about the difference between various available functions which gives us the value of the identity.
Well, here is the difference between three of the important functions:
SELECT @@IDENTITY
It returns the last IDENTITY value produced on a connection, regardless of the table that produced the value, and regardless of the scope of the statement that produced the value. @@IDENTITY will return the last identity value entered into a table in your current session.
SELECT SCOPE_IDENTITY()
It returns the last IDENTITY value produced on a connection and by a statement in the same scope, regardless of the table that produced the value. SCOPE_IDENTITY(), like @@IDENTITY, will return the last identity value created in the current session, but it will also limit it to your current scope as well.
It returns the last IDENTITY value produced in a table, regardless of the connection that created the value, and regardless of the scope of the statement that produced the value. IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the identity value generated for a specific table in any session and any scope.
Remember, you do not want to use @@Identity as that can be also affected by triggers. I personally prefer to use SCOPE_IDENTITY() over @@Identity.
So the question is-
When to use IDENT_CURRENT?
If you have an application where you need to get the latest Identity of the table, instead of the identity which you inserted in the current system, go for ident_current. The values which you may receive might be the same as what you just have inserted or it may be value inserted by another parallel insert as well.
In one line – IDENT_CURRENT function returns the last identity value generated for a specified table or view. The last identity value generated can be for any session and any scope.
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
1 Comment. Leave new
The advice I think should be – always use scope_identity() unless you really do need to use the others. And if you don’t know what they do this advice is the safest. In 17 years I have never found a use case for anything but scope_identity() in the context of a business application.