Obtaining last inserted IDENTITY in table
This article outlines how to obtain last inserted IDENTITY value in a table by any session. IDENT_CURRENT function is used for this. This articles describes how to use this function and what are the limitations of this function.
This article outlines the use of IDENT_CURRENT function. While @@IDENTITY can be used to get last inserted identity value
for the current connection, and SCOPE_IDENTITY can be used to get last inserted identity value for the current scope;
however we can use IDENT_CURRENT function to find the last inserted identity to a table by any session.
If you want to find the last identity value generated for a table by any connection, although you have not recently
inserted a row then you can use IDENT_CURRENT function. Only argument of this function is the table name. This function
returns the last generated identity value for the table passed as argument.
Example:
Create the TABLE1 table in a database.
Create table TABLE1
(
ID INT IDENTITY(1,1)
)
Insert rows with default value into the TABLE1.
Insert into TABLE1 Default values
Now you can use IDENT_CURRENT function to find the last generated identity value.
Select IDENT_CURRENT('TABLE1')
Limitation of IDENT_CURRENT function: Return value may differ from highest identity in table when the latest insertion
is rolled back.
example:
Begin TRAN
insert into TABLE1 default values
RollBack TRAN
Select * from TABLE1
Select IDENT_CURRENT('TABLE1')
