How to Retrieve the Identity of a Newly Inserted Row in SQL Server?

Retrieving the ID of a Newly Added Row in SQL Server: Exploring @@IDENTITY, SCOPE_IDENTITY, IDENT_CURRENT, and the OUTPUT Clause

When adding rows to a table containing an identity column, obtaining the newly generated ID is often crucial. SQL Server offers several methods for achieving this, each with its own advantages and limitations.

@@IDENTITY

This function returns the most recently generated identity value across all tables within the current session. Therefore, the returned value might not always reflect the ID of the row you just inserted. It proves useful when retrieving the ID from a trigger or another statement executed within the same session.

SCOPE_IDENTITY()

SCOPE_IDENTITY() provides the last identity value generated within the current session and scope. This is generally the preferred method for retrieving the ID of the row you've just added.

IDENT_CURRENT('tableName')

This function retrieves the last identity value generated for a specific table, irrespective of the session or scope. This is helpful when you need the ID for a table where you haven't directly inserted a record.

OUTPUT Clause

The INSERT statement's OUTPUT clause allows access to every row inserted by that statement, including identity values. The output can be directed to a temporary table or variable for later retrieval of the ID.

Selecting the Best Approach

The optimal method depends on your specific needs:

• Use @@IDENTITY when obtaining the ID from a trigger or another statement within the same session.
• For the ID of the row you just inserted, SCOPE_IDENTITY() is the recommended approach.
• Employ IDENT_CURRENT('tableName') to retrieve the ID for a table without a recent record insertion.
• For retrieving IDs from multiple rows in a single statement, or for reliable ID retrieval even if errors occur, utilize the OUTPUT clause.

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