The Three Functions at a Glance
All three functions exist for the same purpose — to tell you "what ID did SQL Server just generate for me?". The difference is in scope and session rules.
SCOPE_IDENTITY()
@@IDENTITY
IDENT_CURRENT('tbl')
Two key words to remember: session means a single database connection, and scope means the current batch, stored procedure, or trigger. Hold these two ideas in mind — everything else follows.
The Setup: Two Tables and a Trigger
To see the difference clearly, let's build a small example with two tables — Orders and OrderAudit — and a trigger that logs every new order into the audit table.
CREATE TABLE Orders (
OrderId INT IDENTITY(1, 1) PRIMARY KEY,
Product VARCHAR(50)
);
CREATE TABLE OrderAudit (
AuditId INT IDENTITY(100, 1) PRIMARY KEY,
OrderId INT,
Action VARCHAR(20)
);
-- Trigger: when a row is inserted in Orders,
-- automatically log it into OrderAudit
CREATE TRIGGER trg_LogOrder
ON Orders
AFTER INSERT
AS
BEGIN
INSERT INTO OrderAudit (OrderId, Action)
SELECT OrderId, 'INSERTED' FROM inserted;
END;
Notice the two separate IDENTITY columns: Orders.OrderId starts at 1 and OrderAudit.AuditId starts at 100. This makes the output easy to recognize.
Test 1: Simple INSERT (no trigger effect)
Before we involve the trigger, let's see the baseline. Insert one order and ask all three functions for the last identity value.
INSERT INTO Orders (Product) VALUES ('Laptop');
SELECT SCOPE_IDENTITY() AS Scope,
@@IDENTITY AS AtAt,
IDENT_CURRENT('Orders') AS IdentCurrent;
Result — all three agree because there is no trigger complication yet:
| Scope | AtAt | IdentCurrent |
|---|---|---|
| 1 | 100 | 1 |
Wait — look closely. @@IDENTITY returned 100, not 1! Why? Because the trigger fired and inserted a row into OrderAudit whose IDENTITY starts at 100. @@IDENTITY happily returned that value. This is the classic bug.
Why @@IDENTITY Is Dangerous
@@IDENTITY returns the last identity value generated in the current session, regardless of which table or scope. If an AFTER INSERT trigger inserts into another IDENTITY table, @@IDENTITY picks up that table's value — and your application code returns the wrong ID.
This bug is silent. Your code works for months while there is no trigger. The day someone adds an audit trigger, your "get last inserted ID" query starts returning values like 100, 101, 102 instead of your actual OrderId. Hours of debugging follow.
SCOPE_IDENTITY() Ignores the Trigger
The trigger runs in its own scope — a different scope from your INSERT. SCOPE_IDENTITY() only returns values generated in the same scope that called it. So it correctly returns 1 — the real OrderId your application needs.
SCOPE_IDENTITY(). It is the only safe choice when triggers might exist on the table you are inserting into.
Test 2: Multiple Sessions (where IDENT_CURRENT shines)
Now imagine two users connected to the same database. One is inserting orders, the other is checking reports.
-- SESSION A
INSERT INTO Orders (Product) VALUES ('Keyboard');
-- A just inserted OrderId = 2
-- SESSION B (a totally different connection)
SELECT SCOPE_IDENTITY() AS Scope, -- NULL
@@IDENTITY AS AtAt, -- NULL
IDENT_CURRENT('Orders') AS IdentCurrent; -- 2
In Session B, both SCOPE_IDENTITY() and @@IDENTITY return NULL because Session B hasn't inserted anything. Only IDENT_CURRENT('Orders') can see across sessions and report the latest identity value for the Orders table — the 2 that Session A just generated.
IDENT_CURRENT to grab "the ID of the row I just inserted". Between your INSERT and your SELECT, another user's insert can change the answer. Use it only for diagnostic checks like "what's the highest ID so far in this table".
Summary of Behaviors
| Function | Same session? | Same scope? | Affected by triggers? | Typical use |
|---|---|---|---|---|
SCOPE_IDENTITY() |
Yes | Yes | No (safe) | App code that returns the ID to the user |
@@IDENTITY |
Yes | Any scope | Yes (dangerous) | Legacy code — avoid in new projects |
IDENT_CURRENT('tbl') |
Any session | Any scope | No (safe but cross-session) | Diagnostic: "what's the current IDENTITY on this table" |
Real-World Pattern: Return the Last Inserted ID
This is the most common real use case. You insert a new row, and your application needs the generated ID to return to the user, link to another table, or show on the confirmation page.
CREATE PROCEDURE sp_CreateOrder
@Product VARCHAR(50),
@NewId INT OUTPUT
AS
BEGIN
INSERT INTO Orders (Product) VALUES (@Product);
SET @NewId = SCOPE_IDENTITY();
END;
Clean, safe, and trigger-proof. This is the pattern used in almost every production SQL Server stored procedure.
A Modern Alternative: OUTPUT Clause
In recent versions of SQL Server, you can skip the whole identity-function question and use the OUTPUT clause. It returns the generated value directly as part of the INSERT.
INSERT INTO Orders (Product)
OUTPUT inserted.OrderId
VALUES ('Monitor');
This is the cleanest approach when you insert more than one row at a time — you get all the new IDs in a single result set. For single-row inserts, SCOPE_IDENTITY() is still perfectly fine.
Common Mistakes
Mistake 1: Using @@IDENTITY in application code. The day someone adds a trigger, your app starts returning wrong IDs. Prefer SCOPE_IDENTITY().
Mistake 2: Using IDENT_CURRENT to get the row you just inserted. Another session can race in between your INSERT and SELECT and give you a newer value.
Mistake 3: Expecting any of these to return a value before the INSERT runs. All three return NULL until something generates an identity in the current session (or on the given table).
Mistake 4: Ignoring NULL results. If your session has not inserted anything yet, SCOPE_IDENTITY() and @@IDENTITY return NULL. Always check for that in your code.
Mistake 5: Mixing up table name quoting in IDENT_CURRENT. The table name must be a string: IDENT_CURRENT('Orders'), not IDENT_CURRENT(Orders).
The Interview Answer
"All three return the last auto-generated identity value, but they differ in scope and session.
SCOPE_IDENTITY() returns the last value from the same session and same scope — it is unaffected by triggers, so it is the safest choice. @@IDENTITY returns the last value from the same session across any scope — it can be tricked by triggers and should be avoided. IDENT_CURRENT('table') returns the last value for a specific table across any session or scope — useful for diagnostics but risky for real inserts because another user's insert could change the answer."
Summary
SQL Server gives you three functions to read the last generated identity value, and they behave very differently when triggers or multiple users come into play.
Use SCOPE_IDENTITY() in application code — it's the safe, trigger-proof, same-session-same-scope function. Avoid @@IDENTITY unless you're maintaining old code — it can silently return a wrong value when a trigger fires on another IDENTITY table. Use IDENT_CURRENT('tbl') only for diagnostics or quick checks, never to retrieve an ID tied to your own insert.
And if you want to skip the whole question in modern SQL Server, the OUTPUT clause gives you the new identity value directly and cleanly — especially useful for multi-row inserts.
| Concept | Key Takeaway |
|---|---|
SCOPE_IDENTITY() |
Same session + same scope — safest choice |
@@IDENTITY |
Same session, any scope — broken by triggers |
IDENT_CURRENT('tbl') |
Any session, any scope — diagnostics only |
| Session | A single database connection |
| Scope | Current batch, procedure, or trigger body |
| Trigger trap | @@IDENTITY picks up values from triggered inserts |
| OUTPUT clause | Modern alternative — returns IDs right from INSERT |
| Default choice | Always start with SCOPE_IDENTITY() |
| NULL result | All three return NULL if nothing has been inserted yet |
| Interview line | "Scope-safe, session-unsafe, table-wide — pick one" |