When working with SQL Server, it is often necessary to retrieve the identity value of the last inserted record. This can be useful in various scenarios, such as when you need to perform additional operations or retrieve related data based on the newly inserted record. In this article, we will explore different methods to accomplish this task.

Method 1: SELECT @@IDENTITY

The first method to retrieve the identity of the last inserted record is by using the @@IDENTITY function. This function returns the last identity value produced on a connection, regardless of the table or scope of the statement that produced the value. However, it is important to note that @@IDENTITY is limited to the current session but not limited to the current scope. This means that if a trigger on a table creates an identity value in another table, @@IDENTITY will return the identity value created by the trigger.

Example:

SELECT @@IDENTITY;

Method 2: SELECT SCOPE_IDENTITY()

The second method to retrieve the identity of the last inserted record is by using the SCOPE_IDENTITY() function. Similar to @@IDENTITY, this function returns the last identity value produced on a connection. However, SCOPE_IDENTITY() also limits the value to the current scope. This means that it will only return the last identity value explicitly created by your statement, excluding any identities created by triggers or user-defined functions.

Example:

SELECT SCOPE_IDENTITY();

Method 3: SELECT IDENT_CURRENT(‘tablename’)

The third method to retrieve the identity of the last inserted record is by using the IDENT_CURRENT('tablename') function. This function returns the last identity value produced in a specific table, regardless of the connection or scope of the statement that produced the value. Unlike the previous methods, IDENT_CURRENT is not limited by scope or session; it is limited to the specified table.

Example:

SELECT IDENT_CURRENT('tablename');

When choosing a method to retrieve the identity of the last inserted record, it is important to consider the specific requirements of your application. While @@IDENTITY may be convenient, it can return unexpected results if triggers are involved. On the other hand, SCOPE_IDENTITY() provides a more controlled approach by limiting the value to the current scope. Finally, IDENT_CURRENT allows you to explicitly specify the table and retrieve the identity value regardless of the session or scope.

In conclusion, there are multiple ways to retrieve the identity of the last inserted record in SQL Server. Each method has its own advantages and considerations. It is recommended to choose the method that best suits your specific scenario to ensure accurate and reliable results.

Reference: Pinal Dave (https://blog.sqlauthority.com)