Hello friends, In this tutorial, we are going to see the IDENTITY column in SQL Server. what is the difference between @@IDENTITY, IDENT_CURRENT, and SCOPE_IDENTITY?                                                                                                                                                                                                                             An Identity column in SQL Server is a column that stores the value generated by the SQL Server Database. This is very similar to AutoNumber in SQL Server or Sequence in Oracle.

Introduction:

An Identity column in SQL Server is a column that stores the value generated by the SQL Server Database.
This is very similar to AutoNumber in SQL Server or Sequence in Oracle.
In this tutorial, we are going to discuss different option for Identity Column.

  • @@IDENTITY.
  • IDENT_CURRENT.
  • SCOPE_IDENTITY.

Let’s discuss each of these terms in detail.

Difference between

1- @@IDENTITY:

@@IDENTITY returns last identity value generated for any table in the active sessions across all scopes.

For example:

Suppose I have two tables called table1 and table2 and I have created a trigger on table1 that will insert
record in table2 then @@IDENTITY will return the IDENTITY value of table 2.

2- CURRENT_IDENT:

It returns the last identity value generated for a specific table in any session and any scope. That means it is not affected by Scopes and Sessions it is only related to the specific table.

For example:

if I am using SCOPE_IDENTITY for table1 then it will return IDENTITY value for table1.

3- SCOPE_IDENTITY:

Returns the last identity value inserted into an identity column in the same scope. A scope can be a Stored Procedure, User Defined Function, Trigger or batch. Therefore, if two statements are in the same stored procedure, function, or batch, they are in the same scope.

For example:

Suppose we create an insert trigger on the table which inserts a row in another table with generating an identity column, Then the Scope_IDENTITY will return the IDENTITY value of table on which the record is being inserted.

Example of @IDENTITY, CURRENT_IDENT andSCOPE_IDENTITY:

CREATE TABLE Table1(ID int IDENTITY);

CREATE TABLE Table2(ID int IDENTITY(100,1));

GO

CREATE TRIGGER trgParent ON Table1 FOR INSERT

AS

BEGIN

INSERT Table2 DEFAULT VALUES

END;

GO

--End of trigger definition

SELECT ID FROM Table1;
--id is empty.

SELECT ID FROM Table2;
--ID is empty.

--Do the following in Session 1
INSERT Table1 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 two statements before this query.*/

SELECT IDENT_CURRENT('Table2');

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

SELECT IDENT_CURRENT('Table1');

/* Returns value inserted into Table1. 
This was the INSERT statement four statements before this query.*/

-- 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('Table2');

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

View More:

Conclusion:

I hope you understand what exactly is the difference between @@IDENTITY, IDENT_CURRENT, and SCOPE_IDENTITY.

Thank You.

2 Thoughts to “Difference between @@IDENTITY, IDENT_CURRENT and SCOPE_IDENTITY”

  1. Hello, Many thanks for sharing this article, I liked itvery much! Looking forward to more posts! BTW If you need free themes and plugins, let me know. Have a nice day!

     
  2. Hi, Many thanks for sharing this blog, I really liked it! Looking forward to more posts!

     

Leave a Comment