How to obtain the auto-increment field value after SQL Server inserts data

Scope_identity

Returns the last identity value inserted into the identity column in the same scope. A scope is a module-stored procedure, trigger, function, or batch processing. Therefore, if the two statements are in the same stored procedure, function, or batch processing, they are in the same scope.

Ident_current

Returns the final id value generated for any session and specified table in any scope. This function requires a variable named "Table value". That is to say, although it is not restricted by sessions and scopes, it is restricted by tables.

@ Identity

Returns the last inserted id value.

Experience: with transaction processing, there is no essential difference between two functions and one variable. Two functions without transaction processing are affected by other sessions and scopes.

@ Identity has always been used to obtain the identity value of the last record inserted to the table. Recently, it was found that this method is unreliable in some cases. Let's look at two concepts.
Scope: in the scope of SQL Server, It is a module-stored procedure, trigger, function or batch processing.
Session: All context information generated by a user connection

Similarities: both return the last inserted id value.
Differences:
@ Identity: returns the last Identifier value of the current session, not limited to specific scopes;
Ident_current ('tablename'): returns the last Identifier value generated in a specified table in any session in any scope;
Scope_identity: returns the last Identifier value generated by any table in the current scope of the current session.