T-SQL 101: 132 Identifying the Last Value Inserted with Identity Columns in SQL Server

I showed how identity columns are a special type of constraint. They can be  int or bigint. Both work just fine. What you might need to know though, is the last value inserted automatically by SQL Server.

@@IDENTITY

For a long time, SQL Server only had a single option for this. It was the @@IDENTITY value.

The problem was that it could give you a value different to what you were looking for. The most common situation where this happened is if a trigger was set up. An INSERT trigger says when somebody does an insert, after it’s finished, execute this command as well. But what if that code in the trigger inserted a row somewhere else, perhaps to audit the inserts. The problem is that @@IDENTITY would return back the value from the second INSERT, not the one that you thought it was returning.

Unfortunately, you’ll still see a lot of code that uses @@IDENTITY.

SCOPE_IDENTITY()

In SQL Server 2000, they added the SCOPE_IDENTITY() function. This returns the last identity value in the current scope.

So if a trigger fires after an INSERT, that’s considered to be in a different scope. The function returns the value you expect from inserting into the original table.

If you need to get this value, you almost always should use SCOPE_IDENTITY() and not @@IDENTITY.

Learning T-SQL

It’s worth your while becoming proficient in SQL. If you’d like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

2025-03-20