Differentiate between @@IDENTITY, SCOPE_IDENTITY, IDENT_CURRENT in SQL

Identity columns, also known as AUTO INCREMENT column, is the column whose value is auto incremented by SQL Server on each insert. Data insertion in an IDENTITY column is automatic (not manual - we do not insert any data manually in the identity column, the data is inserted for that IDENTITY column automatically by SQL Server, depending upon the Identity Increment value).

When we need to retrieve the latest inserted row information through SQL Query, we can use SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY functions. All three functions return the last-generated identity values, however, these functions differ on the scope and session on which last is defined.

• @@IDENTITY – It returns the last identity value generated for any table in the current session, across all scopes. @@IDENTITY is not limited to a specific scope.
  Ex: Suppose we create an insert trigger on table which inserts a row in another table which generates an identity column. Then @@IDENTITY returns that identity record which is created by trigger.
• SCOPE_IDENTITY – It returns the last identity value generated for any table in the current session and the current scope.
  Ex: Suppose we create an insert trigger on table which inserts a row in another table which generates an identity column, then SCOPE_IDENTITY result is not affected but if a trigger or a user defined function is affected on the same table that produced the value returns that identity record then SCOPE_IDENTITY returns that identity record which is created by trigger or a user defined function.
• IDENT_CURRENT – It returns the last identity value generated for a specific table in any session and any scope. It is not affected by scope and session and only depends on a particular table. IDENT_CURRENT returns the identity value for a specific table in any session or any scope.

Example: Lets assume that there are two tables, X and Y and both have identity columns. Define an INSERT trigger on X. When a row is inserted to X, the trigger fires and inserts a new row in Y. We can identify two scopes here:

1. INSERT on X
2. INSERT on Y by the trigger

@@IDENTITY and SCOPE_IDENTITY will return different values at the end of an INSERT statement on X. @@IDENTITY will return the last identity column value inserted across any scope in the current session. So, this is the value inserted in Y.

SCOPE_IDENTITY() will return the IDENTITY value inserted in X. This was the last insert that occurred in the same scope. The SCOPE_IDENTITY() function will return null value if the function is invoked before any INSERT statements into an identity column occur in the scope.

Note:

1. Failed statements and transactions can change the current identity for a table and create gaps in the identity column values.
2. 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.

Example:

CREATE TABLE X(id int IDENTITY);

CREATE TABLE Y(id int IDENTITY(100,1));

GO

CREATE TRIGGER XIns ON X FOR INSERT

AS

BEGIN

INSERT Y DEFAULT VALUES

END;

GO

--End of trigger definition

SELECT id FROM X;

--id is empty

SELECT id FROM Y;

--ID is empty

--Do the following in Session 1

INSERT X DEFAULT VALUES;

SELECT @@IDENTITY;

/*Returns the value 100. This was inserted by the trigger.*/

SELECT SCOPE_IDENTITY();

/* Returns the value 1. This was inserted by the

INSERT statement - INSERT X DEFAULT VALUES;*/

SELECT IDENT_CURRENT('Y');

/* Returns value inserted into Y, that is in the trigger.*/

SELECT IDENT_CURRENT('X');

/* Returns value inserted into X.

This was the INSERT statement - INSERT X DEFAULT VALUES;*/

-- Do the following in Session 2.

SELECT @@IDENTITY;

/* Returns NULL because there has been no INSERT action

up to this point in this session.*/

SELECT SCOPE_IDENTITY();

/* Returns NULL because there has been no INSERT action

up to this point in this scope in this session.*/

SELECT IDENT_CURRENT('Y');

/* Returns the last value inserted into Y.*/