SQL Query [scope_identity, ident_current, and @ identity differences (comparison)]

@ Identity (TRANSACT-SQL) returns the system function of the last inserted Identifier value.

Note: 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.

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

Appendix: Basic knowledge of @ identity

1. the identity column cannot be directly updated by the user. It is automatically maintained by the system. 2. The data type of this column must be numeric: int, smallint, tinyint, decimal or numeric with scale 0. 3. This column cannot be null. 4. You cannot set the default value in this column.
5. The increment value can only be an integer (for example, 1, 2,-3 ). It cannot be a decimal or 0. 6. The base value (SEED) can be set by the user. The default value is 1.

@ Identity is the global variable of the current connection and is only valid for the current connection. That is to say, if the connection is closed and then reconnected, @ identity is null. In ADO, @ identity makes sense when the connection object is opened or closed, that is, it is valid within the scope of the connection object. In the MTS component, from opening a connection to an explicit close connection (connection. Close) or before calling setabort or setcomplete, @ identity makes sense during this period. The truncate TABLE statement causes the identity column to start computing again.

[Ident_current (TRANSACT-SQL )]

Ident_current (TRANSACT-SQL) returns the latest Identifier value generated for the table or view specified in a session and scope.

Syntax: ident_current ('table _ name') parameter table_name The Name Of The table whose ID value is returned. The data type of table_name is varchar, with no default value.

Note ident_current is not restricted by the scope and session, but by the specified table. Ident_current returns the value generated for a specific table in any session and scope. When the ident_current function is called in an empty table, this function returns NULL.

 

[Scope_identity (TRANSACT-SQL )]

Scope_identity (TRANSACT-SQL) returns the last Identifier value inserted into the ID column in the same scope. A range 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.

Syntax: scope_identity ()

Remarks

Scope_identity and @ identity return the last id value generated in any table in the current session. However, scope_identity only returns the value inserted into the current scope; @ identity is not limited to a specific scope.

For example, there are two tables T1 and T2, And the insert trigger is defined on T1. When a row is inserted to T1, the trigger is triggered and a row is inserted to T2. This solution demonstrates two scopes: insert on T1 and insert through trigger on T2.

If both T1 and T2 have an ID column, @ identity and scope_identity return different values at the end of the insert Statement on T1. @ Identity returns the value of the last identifier column inserted in any scope of the current session. This is the value inserted in T2. Scope_identity () returns the identity value inserted in T1. This is the last insert in the same scope. If the scope_identity () function is called before any insert statement acts on the identifier column in the scope, the function returns a null value.

[Summary]

@ Identity, scope_identity, and ident_current are similar functions, because they all return the last value inserted to the identity column of the table. All three functions return the last generated id value. However, the "last" scope defined in each of the above functions is different from that defined in sessions. Ident_current returns the latest id value generated for a session and a specified table in the field. @ Identity returns the latest id value generated for a table in the current session across all scopes. Scope_identity returns the latest id value generated for the current session and a table in the current scope.

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

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.

 

[Tips] For example, there are two tables T1 and T2, and an insert trigger is defined on T1. When a row is inserted to T1, the trigger is triggered and a row is inserted to T2. This example illustrates two scopes: insert on T1 and insert on T2 as the trigger result.

Assume that both T1 and T2 have the identity column. @ identity and scope_identity return different values at the end of the insert Statement on T1.

@ Identity returns the value of the last identity column inserted to any scope in the current session. This value is the value inserted in T2.

Scope_identity () returns the identity value inserted in T1, which is the last insert that occurs in the same scope. If the scope_identity () function is called when an insert statement occurs in the scope before the identifiers column, the function returns NULL. The values returned by ident_current ('t1') and ident_current ('t2') are the last auto-increment values of the two tables.

[Example] you may be familiar with @ identity in SQL Server. It is known that it is used to obtain the identity value of the last inserted data in the data table. For example, if Table A has a field with ID auto-increment of 1, assuming that the id value is 100 at this time, if I insert a data entry to Table, after the insert operation, select @ identity returns the value of 101 and the last identity domain (ID domain. Now the question is, why should we use @ identity with caution? The reason is that @ identity always obtains the value of the auto-increment field of the last change data, and ignores the scope constraints of the change operation. 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 ()
It also obtains the value of the last auto-increment field, but it is only within the range of an operation, unlike @ identity, the value of the auto-increment domain generated by the last operation of the global operation is obtained.

Example

The following example shows the different ID values returned by ident_current, @ identity, and scope_identity.

Use adventureworks; go drop table T6; drop table T7; go create table T6 (ID int identity); Create Table T7 (ID int identity (100,1 )); go create trigger t6ins on T6 for insert
As begin insert T7 default values end; go -- end of trigger Definition

Select * From T6; -- ID is empty.

Select * From T7; -- ID is empty.

-- Do the following in session 1 insert T6 default values; select @ identity;/* returns the value 100. This was inserted by the trigger .*/

Select scope_identity ();/* returns the value 1. This was inserted by
Insert statement two statements before this query .*/

Select ident_current ('t7 ');/* returns value inserted into T7, that is in the trigger .*/

Select ident_current ('t6 ');/* returns value inserted into T6. this was the insert statement four statements before this query .*/

-- Do the following in session 2. Select @ identity;/* returns NULL because there has been no insert action up to this point in this session .*/

Select scope_identity ();/* returns NULL because there has been no insert action up to this point in this scope in this session .*/

Select ident_current ('t7 ');/* returns the last value inserted into T7.