Expected Answer (SQL Server 2022)

SQL Server provides three ways to get the last identity, differing by Scope and Session:

1. SCOPE_IDENTITY(): Current session and current scope. Recommended.
2. @@IDENTITY: Current session, any scope (includes triggers).
3. IDENT_CURRENT(‘TableName’): Any session, any scope, specific table.

Why It Matters

Using @@IDENTITY instead of SCOPE_IDENTITY() is a common source of bugs. If a trigger is added to a table that inserts into a log table with its own identity, @@IDENTITY will return the Log ID instead of the Record ID.

SQL Example

-- Standard Retrieval
INSERT INTO Orders (OrderDate) VALUES (GETDATE());
SELECT SCOPE_IDENTITY() AS NewOrderID;

-- SQL Server 2022: Using the OUTPUT clause
INSERT INTO Orders (OrderDate)
OUTPUT inserted.OrderID
VALUES (GETDATE());

Common Mistakes

• Using @@IDENTITY by default: It’s not scope-safe and breaks when triggers are added.
• Using IDENT_CURRENT in multi-user apps: You might get an ID generated by another user’s transaction.

Follow-up Questions

• What happens if an INSERT fails? (Answer: The identity value is still consumed; the next insert will have a gap).
• Alternative to SCOPE_IDENTITY? (Answer: The OUTPUT clause).