Difference between @ identity and @ rowcount in SQL statements

Difference between @ identity and @ rowcount in SQL statements

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.

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.

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

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

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

Copying may affect the @ identity value because it is used in the copy trigger and stored procedure. If this column is part of the copy item, @ identity is not a reliable indicator of the Identity recently created by the user. You can use the scope_identity () function syntax instead of @ identity. For more information, see scope_identity (TRANSACT-SQL ).

USE AdventureWorks2012;GO--Display the value of LocationID in the last row in the table.SELECT MAX(LocationID) FROM Production.Location;GOINSERT INTO Production.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) FROM Production.Location;GO

 

 

 

In a Transact-SQL statement, you can set the value of @ rowcount in the following ways:

Set @ rowcount to the number of affected or read rows. You can send or not send rows to the client.

Keep @ rowcount in the execution of the previous statement.

Reset @ rowcount to 0, but do not return this value to the client.

Run the simple allocation statement to always set the value of @ rowcount to 1. No rows are sent to the client. Examples of these statements are as follows: Set @ local_variable, return, readtext, and select statements without query, such as select getdate () or select 'generic text '.

The statement for allocating in the query or using return sets the value of @ rowcount 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 operation language (DML) Statement sets @ rowcount to the number of rows affected by the query and returns the value to the client. DML statements do not send any rows to the client.

Declare cursor and fetch set the value of @ rowcount to 1.

Execute statement retains the previous @ rowcount.

Use, set <option>, deallocate cursor, close cursor, begin transaction, commit transaction, and other statements to reset the rowcount value to 0.
Example

In the following example, execute the update statement and use @ rowcount to check whether any rows have been changed.

USE AdventureWorks2012;GOUPDATE HumanResources.EmployeeSET JobTitle = N'Executive'WHERE NationalIDNumber = 123456789IF @@ROWCOUNT = 0PRINT 'Warning: No rows were updated';GO