SQL query [difference between scope_identity, ident_current and @ @IDENTITY (comparison)]---reprint

@ @IDENTITY (Transact-SQL) returns the last inserted identity value of the system function.

Note After an INSERT, SELECT into, or bulk copy statement is complete, the @ @IDENTITY contains the last identity value generated by the statement. If the statement does not affect any table that contains an identity column, the @ @IDENTITY returns NULL. If more than one row is inserted and multiple identity values are generated, the @ @IDENTITY returns the last generated identity value. If the statement triggers one or more triggers, and the trigger performs an insert operation that generates an identity value, then the @ @IDENTITY is called immediately after the statement executes to return the last identity value generated by the trigger. If a trigger is triggered after an insert operation on a table that contains an identity column, and the trigger performs an insert operation on another table that does not have an identity column, the @ @IDENTITY returns the identity value that was first inserted. When an INSERT or SELECT into statement fails or a bulk copy fails, or if a transaction is rolled back, the @ @IDENTITY value does not revert to the previous setting.

The @ @IDENTITY function is scoped to the current session on the local server where the function is executed. This function cannot be applied to a remote or linked server. To obtain an identity value on another server, execute the stored procedure on the remote server or linked server and make the stored procedure (executed in the environment of a remote or linked server) collect the identity value and return it to the calling connection on the local server.

Attached: basic knowledge of using @identity

1. The IDENTITY column cannot be updated directly by the user, it is automatically maintained by the system. 2. The column data type must be numeric: int, smallint, tinyint, decimal or numeric with scale 0. 3. The column cannot be null. 4. You cannot set a default value on this column.
5. Increments can only be cosmetic (for example: 1,2,-3). cannot be a decimal, nor can it be 0. 6. The base value (seed) can be set by the user and the default value is 1.

@ @IDENTITY is a global variable for the current connection and is valid only for the current connection. That is, if the connection is disconnected and then reconnected, the @ @IDENTITY is null. For ADO, the @ @IDENTITY is meaningful during Connection object opening and closing, which is valid within the scope of the Connection object's existence. In an MTS component, the @ @IDENTITY is meaningful during this period, either from an open connection to an explicit close connection (connection.close) or until the setabort,setcomplete is called. Using the Truncate table statement causes the IDENTITY column to start the calculation again.

"ident_current (Transact-SQL)"

IDENT_CURRENT (Transact-SQL) returns the most recent identity value generated for a table or view specified in a session and scope.

Syntax: The ident_current (' table_name ') parameter table_name the name of the table whose identity value is returned. The data type of table_name is varchar and has no default value.

Note Ident_current is not subject to scope and session restrictions, but is limited to the specified table. Ident_current returns the value generated for a specific table in any session and scope. When you call the Ident_current function in an empty table, this function returns NULL.

"SCOPE_IDENTITY (Transact-SQL)"

SCOPE_IDENTITY (Transact-SQL) returns the last identity value in the identity column that is inserted into the same scope. A scope is a module: a stored procedure, trigger, function, or batch. Therefore, if two statements are in the same stored procedure, function, or batch, they are in the same scope.

Syntax: scope_identity ()

Note

The scope_identity and @ @IDENTITY return the last identity value generated within any table in the current session. However, scope_identity only returns the value inserted into the current scope, and the @ @IDENTITY is not restricted to a specific scope.

For example, there are two tables T1 and T2, and INSERT triggers are defined on T1. When a row is inserted into the T1, the trigger is fired and a row is inserted in the T2. This scenario demonstrates two scopes: inserts on T1, and inserts in T2 through triggers.

Assuming that both T1 and T2 have identity columns, @ @IDENTITY and scope_identity will return different values at the end of the INSERT statement on T1. @ @IDENTITY Returns the value of the last identity column inserted within any scope in the current session. This is the value inserted in the T2. Scope_identity () returns the IDENTITY value that is inserted in the T1. This is the last insert that occurs within the same scope. If the scope_identity () function is called before any INSERT statement acts on the identity 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 into the IDENTITY column of the table. All three functions return the last generated identity value. However, the "last" scope and the session defined in each of these functions are different. Ident_current returns the most recent identity value generated for a session and the specified table in the domain. @ @IDENTITY Returns the most recent identity value generated for a table in the current session across all scopes. Scope_identity returns the most recent identity value generated for the current session and a table in the current scope.

@ @IDENTITY and scope_identity can return the last identity value generated in all tables in the current session. However, scope_identity only returns values within the current scope, while the @ @IDENTITY is not limited to a specific scope. Ident_current is not subject to scope and session restrictions, but is limited to the specified table. Ident_current can return identity values generated for a particular table in any session and in any scope.

If statements and transactions fail, they change the current identity of the table, causing the values in the identity column to become incoherent. Even if you do not commit a transaction that attempts to insert a value into the table, you can never roll back the identity value. For example, if an INSERT statement fails due to a ignore_dup_key conflict, the current identity value of the table 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 into the T1, the trigger is fired and a row is inserted in the T2. This example illustrates two scopes: one is an insert on T1 and the other is an insert on T2 as the result of a trigger.

Assuming that both T1 and T2 have an IDENTITY column, @ @IDENTITY and scope_identity will return different values at the end of the INSERT statement on T1.

@ @IDENTITY returns the last IDENTITY column value inserted into any scope in the current session, which is the value inserted into the T2.

Scope_identity () returns the IDENTITY value in the Insert T1, which is the last insert that occurs in the same scope. The function returns a NULL value if the scope_identity () function is invoked before the INSERT statement to the IDENTITY column occurs in the scope. The values returned by Ident_current (' T1 ') and ident_current (' T2 ') are the last self-increment values of the two tables respectively.

"Example" Perhaps everyone is unfamiliar with the @ @IDENTITY in SQL Server, knowing that it is the IDENTITY value that gets the last inserted data in the data table. For example, there is a field in table A with an ID of 1, assuming the value of ID is 100, and now if I insert a piece of data into table A and SELECT @ @IDENTITY after insertion, it returns 101, the value of the last IDENTITY field (that is, the ID field). Now that the question is coming, why should we use the @ @IDENTITY with caution? The reason is @ @IDENTITY It always gets the value of the self-increment field of the last change data, ignoring the scope constraint where the change operation occurs. For example, I have table A and table B two tables, and now I have an INSERT trigger defined on table A that automatically inserts a piece of data into table B when you insert a piece of data in table A. 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 domain, so when we insert a piece of data in table A, when we use the SELECT @ @IDENTITY output, is the value of the self-increment field of a or B in the output? The answer is clearly, who is the last person to insert the output, then B. So, I mean to want to get the self-increment domain value of a, the result is the self-increment domain value of B, a BUG with the birth, do not have to affect the whole system of data confusion.

Therefore, for this situation, we recommend that you use the @ @IDENTITY, and try to replace it with the scope_identity () function. Scope_identity ()
It also gets the value of the last self-increment field, but it is only within one operation, not the value of the self-increment field resulting from the last operation of the global operation, unlike @ @IDENTITY.

Example

The following example displays the different identity 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 is inserted by the trigger.*/

SELECT scope_identity (); /* Returns the value 1. This is inserted by the
INSERT statement statements before this query.*/

SELECT ident_current (' T7 '); /* Returns value inserted into T7, that's in the trigger.*/

SELECT ident_current (' T6 '); /* Returns value inserted into T6. This is the INSERT statement four statements before this query.*/

--Do the following in Session 2. SELECT @ @IDENTITY; /* Returns NULL because there have been no INSERT action up to this point in this session.*/

SELECT scope_identity (); /* Returns NULL because there have been no INSERT action up to the this-point-in-scope in this session.*/

SELECT ident_current (' T7 '); /* Returns The last value inserted into T7.

SQL query [difference between scope_identity, ident_current and @ @IDENTITY (comparison)]---reprint