How to Retrieve the Last Identity Value in SQL Server?

Alternative Methods for Retrieving Last Identity Values: A Comprehensive Guide

In database programming, retrieving the value of a primary key identity field after an insert operation is crucial. This article examines various methods for this purpose and provides insights into their functionality and suitability for different scenarios.

@@IDENTITY

@@IDENTITY retrieves the last identity value generated in the current session, irrespective of the table associated with the identity.

declare @t table (
    id int identity primary key,
    somecol datetime default getdate()
)
insert into @t default values

select @@IDENTITY --returns 1

The drawback of @@IDENTITY is that it is not scope-bound. Hence, it can return the last identity value generated by any statement in the session, including those outside the current transaction.

SCOPE_IDENTITY()

SCOPE_IDENTITY() retrieves the last identity value generated within the current scope, which is limited to the statement and transaction that produced it.

select SCOPE_IDENTITY() --returns 1

SCOPE_IDENTITY() is a more precise method compared to @@IDENTITY as it ensures the identity value returned is specific to the current scope.

OUTPUT

Using the OUTPUT clause with an insert statement allows you to return a table containing the inserted rows, including the generated identity values.

Create Table #Testing (  
    id int identity,  
    somedate datetime default getdate()  
)  
insert into #Testing  
output inserted.*  
default values

OUTPUT provides a convenient way to retrieve multiple identity values at once within a single transaction. However, it is specific to the targeted table and requires additional table creation and cleanup operations.

Conclusion

The choice of method for retrieving the last identity value depends on the specific requirements of the application.

• @@IDENTITY is suitable for scenarios where the identity value of any inserted row in the session is needed, regardless of the table.
• SCOPE_IDENTITY() is preferable when the identity value should be specific to the current scope, ensuring accuracy in transactions.
• OUTPUT can be used to retrieve multiple identity values efficiently within a controlled transaction.

The above is the detailed content of How to Retrieve the Last Identity Value in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!