@@IDENTITY, SCOPE_IDENTITY and IDENT_CURRENT in Transact-SQL

In Transact-SQL, you can use @@IDENTITY keyword to retrieve the value of the identity column whenever an INSERT, SELECT INTO or bulk copy statement is completed. It contains the last value that is generated by the statement.

For example, the query below inserts a new record into a table and returns a result set containing the ID of the inserted record.

INSERT INTO myTable (myName) VALUES (‘E-JULY’)

SELECT @@IDENTITY AS [@@IDENTITY] --Last inserted identity value will be inserted

However, beware of a potential for a subtle bug in some circumstances. For example, if you use @@IDENTITY and insert into a table (myTable) that run a trigger, and if the trigger inserts another record into another table (mySubTable), which happens to have an identity column, the @@IDENTITY will contain the identity value of table mySubTable instead of the identity value of myTable.

One option to prevent this from happening, we can use SCOPE_IDENTITY() function instead which will return the last inserted identity value in the current scope, in this case the returned value will be the identity value of myTable. Every trigger, function, procedure and batch has its own scope. SCOPE_IDENTITY shows the last inserted identity value in the current scope, which ignores any triggers that might fire.

INSERT INTO myTable (myName) VALUES (‘E-JULY’)

SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY]

IDENT_CURRENT is limited to a specified table. It returns the last identity value generated for a specific table or view in any session and any scope.

INSERT INTO myTable (myName) VALUES (‘E-JULY’)

SELECT IDENT_CURRENT('myTable') AS [IDENT_CURR]

However, please note that SCOPE_IDENTITY and IDENT_CURRENT are only available in SQL Server 2000 or higher version.