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')


Comments

No responses found. Be the first to comment...


  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: