When to Use IDENT_CURRENT? – Interview Question of the Week #150

Question: When should I use IDENT_CURRENT instead of SCOPE_IDENTITY()?

Answer: I heard this again while co-hosting a GroupBy online conference. It reminded me of my earlier comparison of the three identity functions. The key is to ask what “last” means: last for my statement, my session, or a named table across the whole instance?

@@IDENTITY returns the last identity generated in your session, even if a trigger generated it in a different scope. SCOPE_IDENTITY() stays in your session and current scope, so it is usually the more relevant of those two immediately after a single-row insert. IDENT_CURRENT('schema.table') instead reads the last identity generated for that named table, regardless of which session or scope generated it.

In a disposable lab database, you can see the basic behavior with this example. If another session inserts into the table between your insert and the final query, IDENT_CURRENT can report that other session’s value. That is precisely why it is not a way to retrieve your new row’s ID.

IF OBJECT_ID(N'dbo.IQ150_IdentityLab', N'U') IS NOT NULL
    THROW 50001, 'Choose an unused lab table name.', 1;

CREATE TABLE dbo.IQ150_IdentityLab
(
    ID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    Note nvarchar(30) NOT NULL
);

INSERT INTO dbo.IQ150_IdentityLab (Note)
OUTPUT inserted.ID AS InsertedID
VALUES (N'Lab row');

SELECT SCOPE_IDENTITY() AS SameScopeIdentity,
       @@IDENTITY AS SameSessionAnyScope,
       IDENT_CURRENT(N'dbo.IQ150_IdentityLab') AS NamedTableAnySession;

DROP TABLE dbo.IQ150_IdentityLab;

In this simple single-session lab, all three values will usually match. The OUTPUT inserted.ID result is attached directly to the row the statement inserted, and it also works when an insert affects several rows. The meaning of the three function values remains different even when their numbers happen to be equal.

When do I use IDENT_CURRENT? When I want to inspect the current identity value associated with a particular table, and I understand that another session may have changed it. I do not use it to predict my next ID or to connect a new child row to an insert I just made. Identity values can have gaps, and failed statements or rollbacks can advance the value without leaving a committed row. For the latter task I use the inserted row’s OUTPUT value, or SCOPE_IDENTITY() when the single-row scope is appropriate.

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