@ @IDENTITY and scope_identity () _ Database Digest

After an INSERT, SELECT into, or bulk copy statement completes, the @ @IDENTITY contains the last identifying value of the statement generation. If the statement does not affect any table that contains an identity column, the @ @IDENTITY returns NULL. If more than one row is inserted and more than one identity value is generated, the @ @IDENTITY returns the last generated identity value. If the statement triggers one or more triggers and the trigger performs an insert operation that generates an identity value, calling the @ @IDENTITY immediately after the statement executes returns the last identity value generated by the trigger. If a trigger is triggered after an insert operation on a table that contains an identity column, and the trigger performs an insert operation on another table that does not have an identity column, the @ @IDENTITY returns the identity value that was first inserted. When an INSERT or SELECT into statement fails or a bulk copy fails, or a transaction is rolled back, the @ @IDENTITY value does not revert to the previous setting.

If statements and transactions fail, they change the current identity of the table, causing inconsistencies in the values in the identity column. The identity value can never be rolled back even if a transaction that attempted to insert a value into the table is not committed. For example, if an INSERT statement fails because of a ignore_dup_key conflict, the current identity value of the table will still increase.

@ @IDENTITY, scope_identity, and ident_current are similar functions because they all return the last value inserted into the IDENTITY column of the table.

@ @IDENTITY and scope_identity can return the last identity value generated in all tables in the current session. However, scope_identity only returns values within the current scope, while the @ @IDENTITY is not limited to a specific scope.

Ident_current are not limited by scope and session, but are limited by the specified table. Ident_current can return the identity values generated for a particular table in any session and in any scope.

The scope of the @ @IDENTITY function is the current session on the local server that executes the function. This function cannot be applied to a remote or linked server. To obtain an identity value on a different server, execute the stored procedure on the remote server or linked server and cause the stored procedure (executed in the context of the remote or linked server) to collect the identity value and return it to the connection that made the call on the local server.

The following example inserts a row into the table containing an identity column (LocationID) and uses the @ @IDENTITY to display the identity values used in the new row:

Copy Code code as follows:

Use AdventureWorks;
Go
--display the value of LocationID in the last row in the table.
SELECT MAX (LocationID) from Production.location;
Go
INSERT into Production.location (Name, costrate, availability, ModifiedDate)
VALUES (' Damaged goods ', 5, 2.5, GETDATE ());
Go
SELECT @ @IDENTITY as ' IDENTITY ';
Go
--display the value of LocationID of the newly inserted row.
SELECT MAX (LocationID) from Production.location;
Go

Add:
We should use the @ @IDENTITY with caution, because @ @IDENTITY it always gets the value of the self added field of the last change data.
Ignoring the scope constraint where the change operation is made. For example, I have table A and table B two tables, and now I have an INSERT trigger defined on table A that automatically inserts a piece of data into table B when you insert a piece of data into table A. At this point, notice that there are two atomic operations: inserting a piece of data into a, then inserting a data in B.

Now we want to assume that both table A and table B have the IDENTITY self-amplification field, so after we insert a piece of data in table A, we use the value of the SELECT @ @IDENTITY output, what is the output of a or B's Self amplification field? The answer is obvious, who is the last to insert the output, then B. So, my intention is to get a from the value of the field, the result is the value of the self-added B, a BUG with the birth, it will also affect the entire system data chaos.

Therefore, in this case, we recommend that you use the @ @IDENTITY carefully, and try to use the scope_identity () function to replace it. Scope_identity () is also the value of the last self-amplification field, but it is only within one operating range, not @ @IDENTITY is the value of the field generated by the last operation of the global operation.