1 Pixel Out

I’ve been using the @@IDENTITY keyword for years now to retrieve the ID of a newly created record in SQL Server but we discovered the other day that its behaviour is not always as straight forward as we thought.

The problem

If you run the statement below, in most cases you get the ID of the newly inserted row.

INSERT INTO table_name( ... )
VALUES( ... )

SELECT @@IDENTITY AS newID

The problem arises when your insert statement fires a trigger that performs more inserts to a table with an IDENTITY column. This is the behaviour of the @@IDENTITY keyword according to the Microsoft docs:

If the statement fires one or more triggers that perform inserts that generate identity values, calling @@IDENTITY immediately after the statement returns the last identity value generated by the triggers.

This means that in the situation described above, you wouldn’t get the ID of the new record but the ID of the last record inserted by the trigger.

The solution

Note: Patrick McElhaney commented that this is not the best solution. See the update below.

Use IDENT_CURRENT() instead:

INSERT INTO table_name( ... )
VALUES( ... )

SELECT IDENT_CURRENT('table_name') AS newID

Credit goes to Stuart Miller for pointing this one out as well as finding the solution.

Update

According to Patrick McElhaney’s comment below, SCOPE_IDENTITY() is a better solution. I checked the docs and he’s absolutely right.

This entry was posted on Thursday, April 28th, 2005 at 11:04 pm and is filed under SQL. Both comments and pings are currently closed.