How to Get the Last Inserted Row ID in SQL Server?

Retrieving the ID of the Last Inserted Row in SQL Server

SQL Server offers a streamlined approach to get the newly created ID after a record insertion, especially useful for tables employing the IDENTITY data type.

The Method:

Leveraging IDENTITY Columns

For tables with an INT IDENTITY (or BIGINT IDENTITY) column, use this SQL statement:

<code class="language-sql">INSERT INTO [dbo].[YourTable] ([columns...])
VALUES ([..........])

SELECT SCOPE_IDENTITY();</code>

Explanation:

• The INSERT statement adds a new row.
• SCOPE_IDENTITY() returns the most recently generated IDENTITY value within the current execution.

Important Note:

This method works best when inserting a single row per execution. Inserting multiple rows simultaneously may lead to unpredictable results.

Alternative Approaches:

While SCOPE_IDENTITY() is widely used, other options exist:

• @@IDENTITY: Returns the last generated IDENTITY value for the current session, regardless of subsequent operations in other sessions or scopes.
• IDENT_CURRENT('table_name'): Retrieves the next IDENTITY value scheduled for assignment in the specified table. This is not the ID of the last inserted row and might differ.

For a detailed comparison of these methods and their subtle differences, refer to this insightful article by Pinal Dave: https://www.php.cn/link/c89f28fe875efcf36c8973a7f81bfbb0

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