Difference between IDENT_CURRENT, @@IDENTITY,SCOPE_IDENTITY

It’s good to know the difference between IDENT_CURRENT, @@IDENTITY,SCOPE_IDENTITY. Here is little bit more information about these terms.

IDENT_CURRENT is similar to the SQL Server 2000 identity functions SCOPE_IDENTITY and @@IDENTITY. All three functions return last-generated identity values. However, the scope and session on which last is defined in each of these functions differ:

  • IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope.
  • @@IDENTITY returns the last identity value generated for any table in the current session, across all scopes.
  • SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.

The IDENT_CURRENT function returns NULL when the function is invoked on an empty table or on a table that has no identity column.

Failed statements and transactions can change the current identity for a table and create gaps in the identity column values. The identity value is never rolled back even though the transaction that tried to insert the value into the table is not committed. For example, if an INSERT statement fails because of an IGNORE_DUP_KEY violation, the current identity value for the table is still incremented.

I hope that helps you !

Posted in Microsoft Technology Tagged with:
One comment on “Difference between IDENT_CURRENT, @@IDENTITY,SCOPE_IDENTITY
  1. It should be noted that unless you have a very specific reason to do so, you shouldn’t be using the IDENT_CURRENT() function to snag Identity values. This was around in SQL 2000 as well, and it has the problem that you mention here, the lack of session or scope. SCOPE_IDENTITY() should be the most commonly used function to snag the last identity value created by your session.

Ads