Interview Question: What are the Differences between @@IDENTITY, SCOPE_IDENTITY() and IDENT_CURRENT()

• Download database script - 443 B

Introduction

In this tip, I explain how to get the last inserted identity value of an auto increment column of database tables using SQL Server variable (@@IDENITY) and functions (SCOPE_IDENTITY() and IDENT_CURRENT()). Both @@IDENTITY and SCOPE_IDENTITY() return the last identity value that is produced in a single session; in other words a connection while IDENT_CURRENT() returns the last identity value according to the table for any session.

@@IDENTITY

It returns the last identity value generated for a table in the current session. This table can be any table in the database. So @@IDENTITY has global scope in the database to get the last identity value; that value is generated for any table. It is limited for the current session but not limited for the current scope.

Suppose I have two tables UNIT and SUBUNIT. I have an INSERT trigger on the table UNIT that inserts a row in the table SUBUNIT. Now I insert a row in the table UNIT so my trigger will be fired and will insert a row in the table SUBUNIT also. Now I will get the @@IDENTITY value and it will be the SUBUNIT table's auto increment column last identity value instead of the UNIT table's last identity value.

SCOPE_IDENTITY()

It returns the last identity value generated for a particular table in the current session. It is not only limited to the current session but also limited to the current scope.

Suppose I have two tables UNIT and SUBUNIT. I have an INSERT trigger on the UNIT table that inserts a row in the UNIT table. Now I insert a row in the UNIT table so my trigger will be fired and will insert a row in the SUBUNIT table also. Now I will get the SCOPE_IDENTITY() value, then it will be the UNIT table's auto increment column last identity value instead of the SUBUNIT table's last identity value.

IDENT_CURRENT()

It returns the last identity value generated for any table that is passed as a parameter for it. It is neither limited to the current session nor to the current scope. But it is limited to a table, in other words, it depends on the table that is passed as the parameter. See:

IDENT_CURRENT('Table_Name');

Using the Code

• Create Two Tables

  I create two tables Table UNIT and Table SUBUNIT. I have a seed value different for each table so I can identify what value was obtained from @@IDENTITY and SCOPE_IDENTITY() and for which table.

  SQL

  CREATE TABLE UNIT
  (
          Id INT IDENTITY(1,1),
          Name NVARCHAR(50)
  )
   
  CREATE TABLE SUBUNIT
      (
          Id INT IDENTITY(10,1),
          Name NVARCHAR(50)
    )

• Create Trigger

  I create a trigger on Table UNIT that inserts a row in the SUBUNIT table.

  SQL

  CREATE TRIGGER I_UNIT
  ON UNIT
  FOR INSERT
  AS
  INSERT INTO SUBUNIT(Name)
  VALUES ('gm')

• Insert a row and get @@IDENTITY and SCOPE_IDENTITY()

  I insert a row in the UNIT table and get both of the @@IDENTITY and SCOPE_IDENTITY() values for the insert statement. See:

  SQL

  INSERT INTO UNIT(Name)
  VALUES ('Kg')
   
  SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY()]
  SELECT  IDENT_CURRENT('UNIT') AS [IDENT UNIT]
   
  SELECT @@IDENTITY AS [@@IDENTITY]
  SELECT  IDENT_CURRENT('SUBUNIT')AS [IDENT SUBUNIT]

• OUTPUT

  The following figure shows that we got the @@IDENTITY value from the UNIT table while the SCOPE_IDENTITY() value was from the SUBUNIT table and IDENT_CURRENT() shows the value as a parameter that is passed as the table name.

  

  Figure 1.1 Output of query

Conclusion

Both @@IDENTITY and SCOPE_IDENTITY will the return last identity value in the current session but are different in their scope, but IDENT_CURRENT() does not depend on current session and current scope. I hope it will be helpful for you and if you have any doubt or feedback, then post your comments here or you can directly connect with me at https://twitter.com/ss_shekhawat.