Retrieving IDs after INSERT in SQL Server: Best Practices

1. SCOPE_IDENTITY(): This function is specifically designed to retrieve the identity value generated by the last insert statement within the current scope (usually a single transaction). It's the preferred method for single-row insertions as it's reliable and efficient.

Here's an example:

CREATE TABLE MyTable (
  ID INT IDENTITY(1, 1),
  Name VARCHAR(50)
);

INSERT INTO MyTable (Name)
VALUES ('Alice');

DECLARE @newID int;

SET @newID = SCOPE_IDENTITY();

SELECT @newID;

1. @@IDENTITY: This system function returns the last identity value generated by any INSERT, SELECT INTO, or bulk copy statement. However, it has limitations:

• It only returns the last identity value, so if you inserted multiple rows, you'll only get the ID of the final row.
• It can be affected by triggers that insert into other tables with identity columns.

Due to these drawbacks, SCOPE_IDENTITY() is generally recommended over @@IDENTITY.

1. OUTPUT Clause: For scenarios where you're inserting multiple rows and need all the generated IDs, you can use the OUTPUT clause with the INSERT statement. This clause allows you to capture the inserted data, including the identity values, and store them in a result set.

CREATE TABLE MyTable (
  ID INT IDENTITY(1, 1),
  Name VARCHAR(50)
);

DECLARE @insertedIDs TABLE (ID INT);

INSERT INTO MyTable (Name)
OUTPUT inserted.ID INTO @insertedIDs
VALUES ('Alice'), ('Bob');

SELECT * FROM @insertedIDs;

CREATE TABLE MyTable (
  ID INT IDENTITY(1, 1),
  Name VARCHAR(50)
);

INSERT INTO MyTable (Name)
VALUES ('Alice');

DECLARE @newID int;

SET @newID = SCOPE_IDENTITY();

SELECT @newID;

This code first creates a table MyTable with an identity column ID. Then, it inserts a new row with the name "Alice". The SCOPE_IDENTITY() function is used to store the automatically generated ID for the inserted row in the variable @newID. Finally, a separate SELECT statement retrieves the value of @newID.

Using OUTPUT Clause for multi-row insert:

CREATE TABLE MyTable (
  ID INT IDENTITY(1, 1),
  Name VARCHAR(50)
);

DECLARE @insertedIDs TABLE (ID INT);

INSERT INTO MyTable (Name)
OUTPUT inserted.ID INTO @insertedIDs
VALUES ('Alice'), ('Bob');

SELECT * FROM @insertedIDs;

1. IDENT_CURRENT('table_name'): This function allows you to retrieve the last identity value generated for a specific table, regardless of the scope or transaction. It's useful if you need the ID from an insert that happened in a different stored procedure or trigger.

CREATE TABLE MyTable (
  ID INT IDENTITY(1, 1),
  Name VARCHAR(50)
);

INSERT INTO MyTable (Name)
VALUES ('Alice');

DECLARE @newID int;

SET @newID = IDENT_CURRENT('MyTable');

SELECT @newID;

Important Note: Use IDENT_CURRENT() with caution. It can be less performant than SCOPE_IDENTITY() and might lead to unexpected results if multiple sessions are inserting into the same table concurrently.

1. SELECT TOP 1 with ORDER BY: This approach involves a two-step process. First, you perform a separate SELECT TOP 1 statement with the identity column ordered by descending order. This retrieves the row with the highest ID (which should be the last inserted one).

CREATE TABLE MyTable (
  ID INT IDENTITY(1, 1),
  Name VARCHAR(50)
);

INSERT INTO MyTable (Name)
VALUES ('Alice');

SELECT TOP 1 ID FROM MyTable ORDER BY ID DESC;