Is SQL Server @@IDENTITY a Global Function?

The @@IDENTITY is a system function that returns last generated identity value. But the question is, is it the last identity generated by my code? Last generated by my session? Or last generated by the system for any user?

Understanding what exactly it returns help you to code your database modules accurately. Let’s try this with a small example.

CREATE TABLE T1 (Id int IDENTITY(1,1), Value varchar(20))

CREATE TABLE T2 (Id int IDENTITY(100,1), Value varchar(20))

GO

CREATE PROC InsertT2

AS

BEGIN

    INSERT INTO T2 (Value) VALUES ('Test')

END

GO

CREATE PROC InsertT1

AS

BEGIN

    INSERT INTO T1 (Value) VALUES ('Test')

    EXEC InsertT2

    SELECT @@IDENTITY

    SELECT SCOPE_IDENTITY()

END

GO

EXEC InsertT1

The result of InsertT1 execution will 100 and 1. As you see in the result, @@IDENTITY returns the last generated identity value for my session, not for the scope I am in. If you use this function to get the last generated identity value for your insert, result of it may be wrong unless no other code is executed that inserts a record to another table with IDENTITY property enabled. Note that SCOPE_IDENTITY returns the identity value for the scope, hence if the requirement is for get the value generated for current scope, use it.

There is another function that can be used for getting generated identity value, which is called IDENT_CURRENT. This function accepts the table name as a parameter and returns the last generated identity value for given table, by any scope, any session, any connection.