How to Retrieve the Identity Value of an Inserted Row in SQL Server?

Getting the ID of a Newly Added Row in SQL Server

Adding a row to a table with an auto-incrementing column often requires retrieving the newly generated ID. SQL Server offers several ways to accomplish this, each with its own advantages and disadvantages. Understanding these differences is key to choosing the best method for your situation.

Different Approaches and Their Nuances

@@IDENTITY Function

The @@IDENTITY function returns the most recently generated identity value across all tables within the current database session. This is convenient, but can be unreliable if multiple inserts occur in the same session, potentially leading to incorrect results.

SCOPE_IDENTITY() Function

SCOPE_IDENTITY() is more precise. It returns the last identity value generated within the current session and scope (e.g., within a stored procedure or batch). This makes it generally the safest and most recommended option.

IDENT_CURRENT('tableName') Function

IDENT_CURRENT('tableName') directly targets a specific table. It retrieves the last identity value generated for that table, regardless of the session or scope. This is helpful for checking the last ID assigned to a table, even without a recent insert.

Using the OUTPUT Clause with INSERT

The INSERT statement, when combined with the OUTPUT clause, offers a powerful, direct method. It allows you to capture all rows inserted by the statement, including the identity value. This approach is scoped to the statement itself, avoiding potential ambiguity. Note that this typically involves inserting into a temporary table or table variable, which adds a bit more complexity.

When to Use Which Method

• For most scenarios, SCOPE_IDENTITY() is the preferred method for obtaining the newly generated identity value. Its scope-specific nature ensures accuracy.
• Use @@IDENTITY only when you need the last identity value across all scopes and are aware of the potential for errors.
• IDENT_CURRENT('tableName') is useful for retrieving the last identity value for a specific table, irrespective of the session or scope.
• The INSERT ... OUTPUT approach provides efficiency and scope control but requires extra setup. Consider its benefits against the added complexity.

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