Scope_identity (), @ identity, ident_current () Usage

 

In SQL Server, you can use scope_identity (), @ identity, and ident_current () to obtain the value of the last inserted record. The difference is:

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.

Directly use select scope_identity ()

@ Identity returns the last id value generated in all tables of the current session. If no parameter exists, select @ identity

Ident_current () returns the final id value generated for the specified table in any session and any scope. The parameter is the table name, such as select ident_current ('trole ')

 

 

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.