How to Choose Between @@IDENTITY, SCOPE_IDENTITY(), and OUTPUT for Retrieving Primary Key Identity Values?

Retrieving Last Identity: @@IDENTITY, SCOPE_IDENTITY(), OUTPUT, and More

When retrieving primary key identity field values after insertion, developers often employ various methods. This article delves into the differences and appropriateness of @@IDENTITY, SCOPE_IDENTITY(), and OUTPUT methods.

@@IDENTITY vs. SCOPE_IDENTITY()

@@IDENTITY returns the last IDENTITY value generated for any table, regardless of scope. It's not scope-safe, meaning it may return identities from triggered inserts or other statements within the current session.

SCOPE_IDENTITY() behaves similarly, but it limits the returned value to the current statement and connection scope. Thus, it only retrieves identities generated during the executing statement.

OUTPUT Method

The OUTPUT clause in the INSERT statement returns a table containing the data inserted into the table. This includes the generated IDENTITY values:

INSERT INTO #Testing (ID, somedate)
OUTPUT INSERTED.*
DEFAULT VALUES;

Method Selection

The appropriate method depends on specific requirements:

• @@IDENTITY: Suitable for retrieving the last generated identity across all tables within the current session.
• SCOPE_IDENTITY(): Useful when needing the identity from the current statement and scope only.
• OUTPUT: Ideal for retrieving not just the identity but also other inserted values.

Scope Safety

The OUTPUT method is not explicitly scope-safe. It retrieves identities generated in the current scope, but if those identities were assigned to other tables, it may return them as well. For strict scope safety, use SCOPE_IDENTITY().

Additional Notes

• IDENT_CURRENT(table): Returns the most recent identity value generated for a specific table, regardless of connection or scope. However, it's primarily used in legacy code.

The above is the detailed content of How to Choose Between @@IDENTITY, SCOPE_IDENTITY(), and OUTPUT for Retrieving Primary Key Identity Values?. For more information, please follow other related articles on the PHP Chinese website!