@@IDENTITY, SCOPE_IDENTITY() and IDENT_CURRENT

The @@IDENTITY, SCOPE IDENTITY(), and IDENT_CURRENT functions all return the Identity value (ID value) of the recently (latest) inserted record, however, there is a distinction between them, which is discussed in this article.

@@IDENTITY and SCOPE_IDENTITY return the most recently created identity value in any table in the current session. SCOPE_IDENTITY, on the other hand, returns the value exclusively within the current scope; @@IDENTITY is not bound to a specific scope.

IDENT_CURRENT is neither scope nor session restricted; it is restricted to a specific table. IDENT_CURRENT returns the current identity value for a given table in any session and scope.

@@IDENTITY returns the ID of the most recently inserted record in any table for the current connection or session, but not the current scope. This means it will return newly added even if inserted by a trigger or user-defined function. As a result, only use if you don’t have any triggers or functions that execute automatically.

SCOPE IDENTITY() returns the newly inserted table ID for the current scope and connection or session. This means that it will return the newly inserted ID of the record if it was done by you using a stored procedure or query rather than an automatic process such as a trigger. As a result, to be safe, use SCOPE IDENTITY frequently ()

IDENT_CURRENT(‘TableName’) returns the ID of the most recently inserted record in the supplied table. There are no scope or session constraints here. It will return the ID of the most recent record for that table.

Hope you find this article useful.

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 )

Connecting to %s