How to find the last identity value inserted in the SQL Server

When inserting a row in the database with an identity column as a primary key, most of the time we need to capture the new identity value generated. In SQL Server there cane as many as three approaches for the same.

@@IDENTITY

SCOPE_IDENTITY()

IDENT_CURRENT(‘tablename’)

All of them can be used to find the last identity value inserted in the database, but they differ in the functionality depending on the scope or source of the insert as well as the connection that insert the row.

The server variable @@IDENTITY will return the last generated identity value accross all scope but for the same connection. The value returned will be for the last table inserted with identity column in the same connection. This means that if we insert some record in table (TableA) which has a trigger on the insert and the trigger inserts a record in some other table (TableB) with identity column then the @@IDENTITY will return the identity value inserted in TableB.

Function SCOPE_IDENTITY() is identical to @@IDENTITY with one exception. The value returned is limited to the current scope (i.e. the executed stored procedure). So in our previous example the value returned will the identity value inserted in TableA.

Finally, function IDENT_CURRENT spans all scope and all connections to retrieve the last generated table identity value. But the function is table specific and returns the value for the given table only.

2 thoughts on “How to find the last identity value inserted in the SQL Server

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s