Hard10 minDatabase Fundamentals
UpdatedAug 2, 2026
Edit

@@IDENTITY vs SCOPE_IDENTITY vs IDENT_CURRENT

CONCEPTS:SQL Identity Functions

Question Variations

  • "What is the difference between `@@IDENTITY` and `SCOPE_IDENTITY()` in SQL Server?"
  • "Why is `SCOPE_IDENTITY()` generally preferred over `@@IDENTITY`?"
  • "When would you use `IDENT_CURRENT()` instead of the other two identity functions?"
  • "Explain a scenario where `@@IDENTITY` would return an unexpected value (e.g., due to triggers)."

Why This Is Asked

This is a classic “gotcha” question. Choosing the wrong function can lead to bugs where triggers insert data and the calling code retrieves the wrong ID.

Key Concepts

  • Scope: Current block of execution.
  • Session: Current connection.
  • Table Specificity: Targeting a specific entity.

Question Variations

  • “What is the difference between @@IDENTITY and SCOPE_IDENTITY() in SQL Server?”
  • “Why is SCOPE_IDENTITY() generally preferred over @@IDENTITY?”
  • “When would you use IDENT_CURRENT() instead of the other two identity functions?”
  • “Explain a scenario where @@IDENTITY would return an unexpected value (e.g., due to triggers).”

Answers by Technology

+ Add Variant
SQL ServerImprove this answer ✏️

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).