BlackWaspTM

This web site uses cookies. By using the site you accept the cookie policy.This message is for compliance with the UK ICO law.

SQL Server
SQL 2005+

Obtaining the Last Inserted Identity in Any Session

It is common to obtain the last inserted identity value for a SQL Server table for the current connection, with @@IDENTITY, or for the current scope, with SCOPE_IDENTITY. However, it is also possible to find the latest identity inserted by any session.

IDENT_CURRENT

In an earlier article I described how you can obtain the last inserted identity value using either @@IDENTITY or SCOPE_IDENTITY. These two functions allow you to obtain the identity column value for a row that you have just inserted using the current connection. They differ, as one returns the identity value explicitly inserted in the current scope, whilst the other can give you an identity from an insert that happens in a trigger, which is executed as a by-product of that action.

Sometimes you will want to find that latest identity value created for a table by any connection, even if you have not recently inserted a new row. This is possible using the IDENT_CURRENT function. To use the function you must provide the name of the table to be examined as the only argument. The latest identity for the named table is the return value of the function.

To demonstrate, create the following table in a test database:

CREATE TABLE TestTable
(
    ID INT IDENTITY(1,1)
)

Insert several rows with default values into the test table using the following statement:

INSERT INTO TestTable DEFAULT VALUES

Once you have inserted some rows you can use IDENT_CURRENT to find that latest identity value with the following command:

SELECT IDENT_CURRENT('TestTable')

Limitations

The number returned by IDENT_CURRENT is usually, but not necessarily, the highest identity value that exists in the table. There are two situations that prevent this from being true. The first is when the table is first created and no rows have been inserted. Calling the function for a new, empty table returns the seed value for the identity column. This is the next identity value that will be inserted.

The second case where the returned value will differ from the highest identity is when the latest insertion was made within a transaction that was rolled back. Identity updates not reversed by a rollback so the result of the function will be the value that would have been inserted in this situation. You can see this by running the following script and comparing the highest identity with the result from IDENT_CURRENT.

BEGIN TRAN
INSERT INTO TestTable DEFAULT VALUES
ROLLBACK TRAN
SELECT * FROM TestTable
SELECT IDENT_CURRENT('TestTable')
9 November 2012