Difference between @@IDENTITY, SCOPE_IDENTITY and IDENT_CURRENT

Difference between @@IDENTITY, SCOPE_IDENTITY and IDENT_CURRENT

 

SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY are similar functions because they return values that are inserted into identity columns.

 

IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the value generated for a specific table in any session and any scope.

 

SCOPE_IDENTITY and @@IDENTITY return the last identity values that are generated in any table in the current session. However, SCOPE_IDENTITY returns values inserted only within the current scope; @@IDENTITY is not limited to a specific scope.

For example, there are two tables, T1 and T2, and an INSERT trigger is defined on T1. When a row is inserted to T1, the trigger fires and inserts a row in T2. This scenario illustrates two scopes: the insert on T1, and the insert on T2 by the trigger.

Assuming that both T1 and T2 have identity columns, @@IDENTITY and SCOPE_IDENTITY will return different values at the end of an INSERT statement on T1. @@IDENTITY will return the last identity column value inserted across any scope in the current session. This is the value inserted in T2. SCOPE_IDENTITY() will return the IDENTITY value inserted in T1. This was the last insert that occurred in the same scope. The SCOPE_IDENTITY() function will return the null value if the function is invoked before any INSERT statements into an identity column occur in the scope.

 

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.

 

For live example:

CREATE TABLE TA (

   A_id  int IDENTITY(1,1)PRIMARY KEY,

   A_name varchar(20) NOT NULL)

 

INSERT TA   VALUES ('Ashok')

INSERT TA   VALUES ('Kumar')

INSERT TA   VALUES ('Ajay')

 

CREATE TABLE TB (

   B_id  int IDENTITY(100,5)PRIMARY KEY,

   B_name varchar(20) NULL)

 

INSERT TB (B_name)   VALUES ('HCL')

INSERT TB (B_name)   VALUES ('PRINT')

INSERT TB (B_name)   VALUES ('ZAN')

 

 

/*Create the trigger that inserts a row in table TB

when a row is inserted in table TA.*/

CREATE TRIGGER ATrigg

ON TA

FOR INSERT AS

   BEGIN

   INSERT TB VALUES ('')

   END

 

/*FIRE the trigger and determine what identity values you obtain

with the @@IDENTITY and SCOPE_IDENTITY functions.*/

INSERT TA VALUES ('AK')

 

SELECT * FROM TA

SELECT * FROM TB

 

SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY]

GO

SELECT @@IDENTITY AS [@@IDENTITY]

GO

SELECT IDENT_CURRENT( 'TA') AS [IDENT_CURRENT]

 

Conclusion:

So all three @@IDENTITY, SCOPE_IDENTITY and IDENT_CURRENT returns the last inserted identity value from the table where identity column is defined.

@@IDENTITY returns last inserted identity value in current session.

SCOPE_IDENTITY returns last inserted identity value in current session considering with current scope(refer trigger example).

 

IDENT_CURRENT returns last inserted identity value from associated table and did not depend on session and scope.