PrismitaLEARN · PRACTICE · GROW

What is the difference between SCOPE_IDENTITY(), @@IDENTITY and IDENT_CURRENT()?

SQL Server T-SQL Intermediate 2–3 Yrs3–5 Yrs Conceptual 1 min read

Quick Answer

SCOPE_IDENTITY() — last identity generated in the current scope (your statement/proc); this is the safe choice. @@IDENTITY — last identity anywhere in the session, so a trigger's insert can return the wrong value. IDENT_CURRENT('Table') — last identity for a specific table across all sessions, subject to race conditions.

SCOPE_IDENTITY() gives you the last identity value generated in your own scope, so it's the one to use after a single-row insert. @@IDENTITY is session-wide, which means if the table has an insert trigger that writes to another identity table, you get that value instead — a nasty bug that only shows up when someone adds the trigger later. IDENT_CURRENT is per-table but across all sessions, so it's racy. The cleanest option overall is the OUTPUT clause, which also handles multi-row inserts.

  • Prefer the OUTPUT clause — it's unambiguous and works for multi-row inserts.
  • @@IDENTITY bugs are subtle: they appear only after someone adds a trigger months later.
  • IDENT_CURRENT is for diagnostics, never for “what id did I just insert”.
All SQL Server questions More on T-SQL