The difference between @ @IDENTITY and @ @ROWCOUNT in SQL Drip 31-sql statement

Source: The difference between @ @IDENTITY and @ @ROWCOUNT in SQL Drip 31-sql statement

difference between @ @IDENTITY and @ @ROWCOUNT in SQL statements

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.

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.

@ @IDENTITY, scope_identity, and ident_current are similar functions because they all return the last value inserted into the IDENTITY column of the table.

@ @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. For more information, see IDENT_CURRENT (Transact-SQL).

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.

Replication may affect the @ @IDENTITY value because it is used in replication triggers and stored procedures. If this column is part of a replicated project, the @ @IDENTITY is not a reliable indicator of the most recent user-created identity. You can use the scope_identity () function syntax instead of @ @IDENTITY. For more information, see SCOPE_IDENTITY (Transact-SQL).

 UseAdventureWorks2012;GO--Display The value of LocationID in the last row in the table.SELECT MAX(LocationID) fromproduction.location;GOINSERT  intoproduction.location (Name, costrate, availability, ModifiedDate)VALUES('damaged Goods',5,2.5,GETDATE());GOSELECT @ @IDENTITY  as 'Identity';GO--Display The value of LocationID of the newly inserted row.SELECT MAX(LocationID) fromproduction.location;GO

Transact-SQL statements can set the value of the @ @ROWCOUNT in the following ways:

Sets the @ @ROWCOUNT to the number of rows affected or read. Rows can be sent to the client or not sent.

Keep the @ @ROWCOUNT in the previous statement execution.

Resets the @ @ROWCOUNT to 0 but does not return the value to the client.

A statement that performs a simple assignment always sets the @ @ROWCOUNT value to 1. No rows are sent to the client. Examples of these statements are: SET @local_variable, RETURN, READTEXT, and SELECT statements without queries, such as select GETDATE () or select ' Generic Text '.

The statement that is allocated in the query or uses RETURN will set the @ @ROWCOUNT value to the number of rows affected by the query or the number of rows read by the query, for example: SELECT @local_variable = C1 from t1.

The Data Manipulation language (DML) statement sets the @ @ROWCOUNT value to the number of rows affected by the query and returns that value to the client. The DML statement does not send any rows to the client.

DECLARE CURSOR and FETCH set the @ @ROWCOUNT value to 1.

The EXECUTE statement retains the previous @ @ROWCOUNT.

Statements such as use, SET <option>, deallocate cursor, CLOSE cursor, BEGIN TRANSACTION, or COMMIT TRANSACTION reset the ROWCOUNT value to 0.
Example

The following example executes the UPDATE statement and uses the @ @ROWCOUNT to detect whether any of the rows have changed.

 UseAdventureWorks2012;GOUPDATEHumanResources.EmployeeSETJobTitle=N'Executive'WHERENationalidnumber= 123456789IF @ @ROWCOUNT = 0PRINT 'warning:no rows were updated';GO

The difference between @ @IDENTITY and @ @ROWCOUNT in SQL Drip 31-sql statement