SQL SCOPE_IDENTITY() and @@IDENTITY Differences in Retrieving Auto-Increment Values

March 20, 2023 Program

Requirement
Principle
Example
Conclusion

Requirement
Principle
Example
Conclusion

Retrieve the Most Recently Added Data in SQL

Requirement

In frontend development, it is common to require subsequent processing of newly added data, which often involves the use of the IDENTITY value of the added data. Similarly, in backend databases, triggers may be used to modify or add data, and the IDENTITY value of the newly added data may also be required for subsequent processing. This article will discuss the practical differences between SCOPE_IDENTITY() and @@IDENTITY in SQL Server.

Principle

🔗

SCOPE_IDENTITY() function returns the most recently inserted identity value within the current scope. This means that it only returns the identity value generated by the insert statement currently being executed, and is not affected by any identity values generated by any triggers.

@@IDENTITY function returns the last identity value generated by an insert statement, regardless of the scope in which it was generated. Therefore, if the insert statement includes triggers, the @@IDENTITY function may return an unexpected identity value.

It may sound a bit complicated, so let's provide an example to illustrate the differences between the two functions.

Example

🔗

1.Create an example table

SQL
CREATE TABLE ExampleTable (
  ID INT PRIMARY KEY IDENTITY(1,1),
  Name NVARCHAR(50) NOT NULL
);

2.Create an trigger if there is a newly data inserted that copy the newly data to another row in the same table.

SQL
CREATE TRIGGER ExampleTrigger
ON ExampleTable
FOR INSERT
AS
BEGIN
  INSERT INTO ExampleTable (Name)
  SELECT Name FROM INSERTED
END;

3.Insert a row with Name "Alvin" to the table, which will activate the trigger and create another row with the same data. As a result, the table will have two rows with Name "Alvin".

SQL
INSERT INTO ExampleTable (Name)
VALUES ('Alvin');

SELECT SCOPE_IDENTITY()
SELECT @@IDENTITY

DROP TABLE ExampleTable;

The content of the table is as follows:

ID	Name
1	Alvin
2	Alvin

The result of SELECT SCOPE_IDENTITY() is 1.
The result of SELECT @@IDENTITY is 2.

It is clear that SCOPE_IDENTITY() retrieves the ID of the current insert, while @@IDENTITY retrieves the ID of the data inserted by the trigger.

Conclusion

🔗

When retrieving the value of an auto-incremented field for subsequent processing of the newly inserted data, it is recommended to use SCOPE_IDENTITY() rather than @@IDENTITY. Using @@IDENTITY may result in obtaining an additional value that was inserted due to the current insertion, whereas SCOPE_IDENTITY() specifically retrieves the ID of the newly inserted data.

SQL