This is the third part of this blog series. You can read the 2nd part here HereIn this blog post we’ll learn how to retrieve last generated identity value. There are scenarios where we need to fetch the last inserted identity value, for example in a transaction we may first generate OrderID for order header table and then enter item details for that order in OrderItem table.

SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY all of them are used to retrieve last inserted identity value but all of them return value depending on scope and session. Let us see how can we fetch last inserted identity value in SQL Server.

SCOPE_IDENTITY:

It returns the last Identity value inserted within the current scope for any table. A scope is a module that can be a stored procedure or trigger or function or batch. It returns identity value. This function will return NULL if it is invoked before any insert statements into an identity column occurs in the scope.

Example 1:

identity value using Scope Identity

Example 2:

identity value impact on trigger

IDENT_CURRENT

It returns the last IDENTITY value produced in a table, irrespective of the connection and the scope of the statement. It is not dependent on any scope and session. This function takes table name as parameter.

Example 1:

identity value through IDENT_CURRENT

@@IDENTITY

This system function returns the last generated identity value for any table in the current session and is not limited to any specific scope.

If any insert statement on a table causes a trigger to fire on another table which cause to generate an identity value, you will get the last identity value that was created last.

Example 1:

identity value through @@Identity