SQL Server gets the last inserted ID value scope_identity, ident_current, and @ @IDENTITY comparison

• Ident_current returns the identity value that was last generated for any session and for a particular table in any scope. Ident_current is not subject to scope and session restrictions, but is limited to the specified table.
• @ @IDENTITY Returns the identity value that was last generated for any table in all scopes of the current session.
• Scope_identity returns the identity value that was last generated for the current session and any tables in the current scope

Perhaps everyone is unfamiliar with the @ @IDENTITY in SQL  server, knowing that it is the IDENTITY value that gets the last insert data in the data table.
For example, there is a field in table A with an ID of 1, assuming that the value of the ID is 100, and now if I insert a piece of data into table A and   after it is inserted;
SELECT @ @IDENTITY, it returns 101, last The value of an identity domain (that is, the ID field).

Now that's the problem, why use @ @IDENTITY with caution? The reason is @ @IDENTITY It always gets the value of the self-increment field of the last change data, and
ignores the scope constraint where the change operation occurs. For example, I have table A and table B two tables, and now I have defined one insert trigger on table A,
when inserting a piece of data in table A, automatically inserts a piece of data in table B. At this point, it is noted that there are two atomic operations: inserting a piece of data into a and then inserting a piece of data in B.

Now we want to assume that both table A and table B above have an IDENTITY increment field, so we use   when we insert a piece of data in Table A, and
SELECT @ @IDENTITY output, which is the value of the self-increment field of a or B? The answer is obvious, who is the last insert,
then B. So, I mean to want to get a self-increment field value, the result got the self-increment domain value of B, a BUG was born, and the
will affect the whole system data confusion.

Therefore, it is recommended that you use @ @IDENTITY with caution and replace it with the  scope_identity ()   function as much as possible. Scope_identity ()  
also gets the value of the last one self-increment field, but it is limited to one operation, not like @ @IDENTITY is the value of the auto-increment field generated by the last step of the global operation.

SQL Server gets the last inserted ID value scope_identity, ident_current, and @ @IDENTITY comparison