@ IDENTITY and SCOPE_IDENTITY ()

After an INSERT, select into, or large-capacity copy statement is completed, @ IDENTITY contains the last Identifier value generated by the statement. If the statement does not affect any tables that contain the ID column, @ IDENTITY returns NULL. If multiple rows are inserted and Multiple ID values are generated, @ IDENTITY returns the last generated id value. If a statement triggers one or more triggers and the trigger inserts an id value, call @ IDENTITY immediately after the statement is executed. The last id value generated by the trigger is returned. If a trigger is triggered after the insert operation is performed on the table that contains the ID column, and the trigger inserts the table that does not have the ID column, then @ IDENTITY returns the id value inserted for the first time. When the INSERT or select into statement fails, the large-capacity copy fails, or the transaction is rolled back, the @ IDENTITY value is not restored to the previous setting.

If the statement and transaction fail, they will change the current identifier of the table, causing inconsistency of values in the ID column. Even if you have not committed a transaction that tries to insert a value to the table, you will never be able to roll back the id value. For example, if the INSERT statement fails due to an IGNORE_DUP_KEY conflict, the current table id value will still increase.

@ IDENTITY, SCOPE_IDENTITY, and IDENT_CURRENT are similar functions, because they all return the last value inserted to the IDENTITY column of the table.

@ IDENTITY and SCOPE_IDENTITY can return the last id value generated in all tables in the current session. However, SCOPE_IDENTITY only returns values within the current scope, and @ IDENTITY is not limited to specific scopes.

IDENT_CURRENT is not restricted by the scope and session, but by the specified table. IDENT_CURRENT can return the id value generated for a specific table in any session and in any scope.

@ IDENTITY the function scope is the current session on the local server where the function is executed. This function cannot be applied to remote or linked servers. To obtain the id value of another server, run the stored procedure on the remote server or linked server and run the Stored Procedure (executed in the remote or linked server environment) this stored procedure collects the identity value and returns it to the connection on the local server for calling.

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

Copy codeThe Code is 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 'identified ';
GO
-- Display the value of LocationID of the newly inserted row.
Select max (LocationID) FROM Production. Location;
GO

Supplement:
Use @ IDENTITY with caution because @ IDENTITY always obtains the value of the auto-increment field of the last modified data,
The scope constraints of the change operation are ignored. For example, I have two tables, table A and Table B. Now I have defined an Insert trigger on Table A. When A data entry is inserted in Table, A data entry is automatically inserted in table B. Note that there are two atomic operations: Insert A piece of data in A, and then insert A piece of data in B.

Now, let's assume that both table A and table B have the IDENTITY auto-incrementing domain. When we insert A piece of data in Table A and use the SELECT @ IDENTITY output, is the output A or B's auto-incrementing Domain value? The answer is obvious: B is the output of the last insert. As A result, I wanted to get the auto-increment Domain value of A, and the result was that the self-increment Domain value of B was obtained. A BUG was born along with it, which would affect the data chaos of the entire system.

Therefore, we recommend that you use @ IDENTITY with caution, and use the SCOPE_IDENTITY () function whenever possible. SCOPE_IDENTITY () is also the value of the last auto-increment domain, but it is only within the range of one operation, not @ IDENTITY is the value of the auto-increment domain generated by the last operation of the global operation.