Interview Question of the Week #030 – Retrieve Last Inserted Identity of Record

This is a classic interview question because the three familiar identity functions sound similar until a trigger or another session changes the answer.

Question: How do you retrieve the identity value of the row you just inserted?

Answer: For a single-row insert, I usually capture SCOPE_IDENTITY() immediately in the same batch or procedure. For a statement that inserts several rows, use OUTPUT INSERTED to capture each generated key. The three functions in my original answer have different boundaries:

Suppose your insert into Orders fires a trigger that inserts an identity row into OrderAudit. The trigger is another scope. @@IDENTITY can report the audit key, while SCOPE_IDENTITY() reports the order key. IDENT_CURRENT('Orders') reports the latest key for the table across sessions, so it cannot establish which order belongs to your connection.

Here is a standalone single-row example. Capture the value immediately after the insert in the same scope:

DECLARE @Orders TABLE
(
    OrderId int IDENTITY(1,1) PRIMARY KEY,
    CustomerId int NOT NULL
);

INSERT @Orders (CustomerId) VALUES (42);
DECLARE @OrderId int = CONVERT(int, SCOPE_IDENTITY());
SELECT @OrderId AS NewOrderId;

Identity functions return a numeric type, so convert to the key’s actual type. A failed or rolled-back insert can still consume an identity value. Never assume identity values are gap-free.

For multiple rows, a scalar last-value function loses information. Capture the keys from the insert statement itself:

GO
DECLARE @Orders TABLE
(
    OrderId int IDENTITY(1,1) PRIMARY KEY,
    CustomerId int NOT NULL
);
DECLARE @NewOrders TABLE (OrderId int NOT NULL, CustomerId int NOT NULL);

INSERT @Orders (CustomerId)
OUTPUT inserted.OrderId, inserted.CustomerId
INTO @NewOrders (OrderId, CustomerId)
VALUES (42), (43);

SELECT OrderId, CustomerId FROM @NewOrders ORDER BY OrderId;

If a permanent target table has an enabled trigger, OUTPUT INTO is the appropriate form. For a real multi-row workflow, include a stable business key in the captured mapping when you must relate each new identity to its source row. The interview answer is to choose the boundary that matches the insert.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.