It is a common task 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.
In a previous 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:
Insert several rows with default values into the test table using the following statement:
Once you have inserted some rows you can use IDENT_CURRENT to find that latest identity value with the following command:
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.