How to Choose Between @@IDENTITY, SCOPE_IDENTITY(), and OUTPUT for Retrieving Last Identity Values?

Retrieving Last Identity: @@IDENTITY, SCOPE_IDENTITY(), and OUTPUT

Inserting new records into a table often involves retrieving the last generated identity value for the column. Various methods exist for this purpose, including @@IDENTITY, SCOPE_IDENTITY(), and OUTPUT.

@@IDENTITY

@@IDENTITY retrieves the last identity value produced on the current connection, regardless of the table or scope. It returns the identity value generated by the most recent insert, even if triggered by another operation. However, this method is not scope-safe.

SCOPE_IDENTITY()

SCOPE_IDENTITY(), on the other hand, is scope-safe. It returns the last identity value generated within the current scope of the statement. This means that if a trigger inserts a record and generates an identity value, SCOPE_IDENTITY() will not return that value unless it was generated by the same statement that called SCOPE_IDENTITY().

OUTPUT Method

The OUTPUT method is another option for retrieving the identity value. It returns a table of inserted rows, including the identity values generated during the insert. This method is also scope-safe, as it only retrieves the identity values for the rows inserted by the current statement.

Deciding the Best Method

The choice of method for retrieving the last identity value depends on the specific scenario:

• @@IDENTITY: Suitable for retrieving the last identity value in a specific session, regardless of scope.
• SCOPE_IDENTITY(): Use when it's crucial to retrieve the identity value within the same scope as the insert statement.
• OUTPUT Method: Ideal when you need to retrieve a table of inserted rows, including their identity values.

The above is the detailed content of How to Choose Between @@IDENTITY, SCOPE_IDENTITY(), and OUTPUT for Retrieving Last Identity Values?. For more information, please follow other related articles on the PHP Chinese website!