What's the Difference Between `@@IDENTITY`, `SCOPE_IDENTITY()`, `IDENT_CURRENT()`, and `IDENTITY()` in SQL Server?

SQL Server Identity Functions: A Detailed Comparison

@@IDENTITY, SCOPE_IDENTITY(), IDENT_CURRENT(), and IDENTITY() all relate to retrieving values from identity columns in SQL Server, but they differ significantly in scope and application.

Understanding Scope in SQL Server Identity Functions

"Scope" refers to the context where the identity value is generated. This can be:

• Session Scope: The current database connection.
• Query/Stored Procedure Scope: The current execution block of a query or stored procedure.

Function Breakdown

• @@IDENTITY: Returns the last identity value generated in the current session, regardless of the scope (query, stored procedure, or trigger).

• SCOPE_IDENTITY(): Returns the last identity value generated within the current session and scope. This is the most commonly recommended function because it provides more precise results.

• IDENT_CURRENT('table_name'): Returns the last identity value generated for a specific table, irrespective of the session or scope. This is useful when you need the last identity value for a particular table across different sessions or contexts.

• IDENTITY(): This is not used for retrieving identity values. It's a keyword used to define an identity column when creating a table using SELECT ... INTO syntax.

Illustrative Scenarios

Let's examine different scenarios to highlight the differences:

• Single INSERT Statement: If a single INSERT statement adds a row, SCOPE_IDENTITY() and @@IDENTITY will return the same value.

• Trigger-Based Insertion: If a trigger inserts a row into a second table after an INSERT into the first table, SCOPE_IDENTITY() will return the identity from the table directly affected by the current statement (the first table), while @@IDENTITY will return the identity from the table affected last in the session (which might be the second table).

Choosing the Right Function

For most cases, SCOPE_IDENTITY() is preferred as it provides context-specific results. @@IDENTITY is helpful when you need the most recent identity value regardless of its origin within the session. IDENT_CURRENT() is crucial when you need the last identity value for a particular table, irrespective of the current session. Remember, IDENTITY() is for defining identity columns, not retrieving their values.

The above is the detailed content of What's the Difference Between `@@IDENTITY`, `SCOPE_IDENTITY()`, `IDENT_CURRENT()`, and `IDENTITY()` in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!