Understanding IDENT_CURRENT, SCOPE_IDENTITY, and @@IDENTITY in SQL Server

In SQL Server, identity columns are often used with auto-generate primary keys for new rows.

Retrieving the last generated identity value seems simple — until you discover that three different functions (@@IDENTITY, SCOPE_IDENTITY(), and IDENT_CURRENT('table')) can all return different results under certain conditions.

In one of my recent projects, I noticed the development team was using a mix of these functions interchangeably. At first glance it looks fine, but multiple inserts and triggers can make things go wrong.

The Three Identity Functions Explained

@@IDENTITY

• Returns the last identity value generated in the current session, but across all scopes.
• This includes inserts performed by triggers.
• Risk: If your insert fires a trigger that inserts into another table with its own identity, @@IDENTITY returns that value not the one you expected.

SCOPE_IDENTITY()

• Returns the last identity generated in the current session and the current scope.
• It’s safe against triggers, because it ignores identity values generated in other scopes (like those inside a trigger or nested procedure).
• Recommended when you need the identity value you just inserted.

IDENT_CURRENT('table_name')

• Returns the last identity value for a specific table, regardless of session or scope.
• Danger: In a multi-user environment, another user’s insert can change the returned value between your insert and your select.

Session vs. Scope:  What’s the Difference?

• A session is a connection between the client and SQL Server (identified by a SPID). It lasts from when you connect until you disconnect.

• A scope is a boundary inside that session, for example:

• A stored procedure
• A trigger
• A batch of T-SQL statements

• SCOPE_IDENTITY() respects both boundaries.
• @@IDENTITY ignores scope boundaries.
• IDENT_CURRENT() ignores both.

Why Mixing Them Causes Problems 

When different developers use different identity functions within the same application or stored procedures, the result is non-deterministic identity retrieval, especially under concurrency.

Example:

1. Developer A uses SCOPE_IDENTITY() to retrieve IDs after inserts (correct).
2. Developer B uses IDENT_CURRENT('Table') in another procedure (unsafe under load).
3. A trigger fires and causes @@IDENTITY to return unexpected values.

The result:

• Wrong foreign key references.
• Broken relationships.
• Hard-to-reproduce data integrity bugs.