@ Identity: returns the last id value generated in all tables in the current session.

 

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. For more information, see ident_current (TRANSACT-SQL ).

@ 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.