Find the IDENTITY value of the last inserted row (SQL Server)

@@IDENTITY, SCOPE_IDENTITY() or IDENT_CURRENT()? What's the difference? And which one should you use?

By Lynn Evans

A common requirement in database programming is to obtain the value of an auto-incremented primary key for the most recently inserted row. You would typically do that when adding a child row immediately after adding its parent. You need to know the primary key of the parent so that it can be inserted into the child's foreign key column.

In SQL Server, the IDENTITY attribute is used to auto-generate a primary key. T-SQL provides several ways of programmatically determining the value of the IDENTITY column in the most recently added row, but it's not always clear which is the best method to use.

Find the highest value

One approach would be to simply look for the current highest value in the IDENTITY column. You could do that using either SELECT MAX( … ) or SELECT TOP 1 … ORDER BY…. That would work, but only if you could be sure that no other user had inserted a row at the same time. If they had, you'd risk getting that user's IDENTITY value rather than your own.

@@IDENTITY

The @@IDENTITY system function yields the most recent IDENTITY value for the current connection, so there is no danger of getting another user's value. But you need to keep in mind that it is not restricted to the current scope, nor to any particular table. This could be an issue in certain cases, most typically where triggers are involved.

To illustrate, suppose you are inserting a row into a transaction table. The table has an INSERT trigger, which inserts a row into an audit table. If you examine the value of @@IDENTITY after the insert into the transaction table, the value you see will have the IDENTITY value, not of the transaction table, but of the audit table. This is probably not what you want. (Note that this is only true if the audit table itself has an IDENTITY column; if it hasn't, the value will be that of the transaction table.)

IDENT_CURRENT()

The IDENT_CURRENT() function simply returns the highest IDENTITY value (for a specified table), regardless of session and scope. In that respect, it behaves the same as SELECT MAX( … ) and SELECT TOP 1 … ORDER BY….

SCOPE_IDENTITY()

The SCOPE_IDENTITY() function returns the IDENTITY value for any table, but only in the current session and current scope. In most cases, this will be the best method to use. The only proviso is that, if you are inserting rows into more than one table, you need to call SCOPE_IDENTITY() immediately after the INSERT for the table whose value you need to retrieve.

More points

All the above methods work for SELECT … INTO and bulk copy statements in the same way as for INSERT statements. Where a single statement inserts more than one row, the returned value is that of the highest row.

In all cases, if the statement that inserts the row(s) fails or is rolled back, the IDENTITY value is not itself rolled back. This could lead to gaps in the sequence in the relevant column. This is normal behavior with auto-incrementing keys.

January 2012

Please note: The information given on this site has been carefully checked and is believed to be correct, but no legal liability can be accepted for its use. Do not use code, components or techniques unless you are satisfied that they will work correctly with your sites or applications.

If you found this article useful, please tell your friends: