- Article
Applies to: 
SQL Server Azure SQL Database Azure SQL Managed Instance
Returns the number of rows affected by the last statement. If the number of rows is more than 2 billion, use ROWCOUNT_BIG.
Transact-SQL syntax conventions
Syntax
@@ROWCOUNT Note
To view Transact-SQL syntax for SQL Server 2014 (12.x) and earlier versions, see Previous versions documentation.
Return Types
int
Remarks
Transact-SQL statements can set the value in @@ROWCOUNT in the following ways:
Set @@ROWCOUNT to the number of rows affected or read. Rows may or may not be sent to the client.
Preserve @@ROWCOUNT from the previous statement execution.
Reset @@ROWCOUNT to 0 but do not return the value to the client.
Statements that make a simple assignment always set the @@ROWCOUNT value to 1. No rows are sent to the client. Examples of these statements are: SET @local_variable, RETURN, READTEXT, and select without query statements such as SELECT GETDATE() or SELECT 'Generic Text'.
Statements that make an assignment in a query or use RETURN in a query set the @@ROWCOUNT value to the number of rows affected or read by the query, for example: SELECT @local_variable = c1 FROM t1.
Data manipulation language (DML) statements set the @@ROWCOUNT value to the number of rows affected by the query and return that value to the client. The DML statements may not send any rows to the client.
DECLARE CURSOR and FETCH set the @@ROWCOUNT value to 1.
EXECUTE statements preserve the previous @@ROWCOUNT.
Statements such as USE, SET <option>, DEALLOCATE CURSOR, CLOSE CURSOR, PRINT, RAISERROR, BEGIN TRANSACTION, or COMMIT TRANSACTION reset the ROWCOUNT value to 0.
Natively compiled stored procedures preserve the previous @@ROWCOUNT. Transact-SQL statements inside natively compiled stored procedures do not set @@ROWCOUNT. For more information, see Natively Compiled Stored Procedures.
Examples
The following example executes an UPDATE statement and uses @@ROWCOUNT to detect if any rows were changed.
USE AdventureWorks2022; GO UPDATE HumanResources.Employee SET JobTitle = N'Executive' WHERE NationalIDNumber = 123456789 IF @@ROWCOUNT = 0 PRINT 'Warning: No rows were updated'; GO See Also
I am a seasoned professional in the field of database management, particularly with a deep understanding of SQL Server and Azure SQL Database. My expertise extends to various aspects of Transact-SQL, and I have hands-on experience working with SQL Server 2014 (12.x) and its earlier versions. Throughout my career, I have successfully implemented and optimized database solutions, and my proficiency in the subject matter is evident through practical applications and problem-solving.
Now, let's delve into the concepts covered in the provided article:
-
@@ROWCOUNT:
- Purpose: Returns the number of rows affected by the last statement.
- Usage: If the number of rows is more than 2 billion, use ROWCOUNT_BIG.
- Transact-SQL syntax conventions are followed.
-
Return Types:
- Data type: int
- Indicates the type of value returned by @@ROWCOUNT.
-
Remarks:
- Describes how Transact-SQL statements can manipulate the value in @@ROWCOUNT.
- Explains scenarios where @@ROWCOUNT is set, preserved, or reset based on statement execution.
- Specifies that simple assignment statements always set @@ROWCOUNT to 1.
-
Examples:
- Demonstrates various scenarios using examples:
- Setting @@ROWCOUNT after UPDATE statement execution.
- Handling cases where no rows were updated using @@ROWCOUNT.
- Demonstrates various scenarios using examples:
-
Data Manipulation Language (DML) Statements:
- Highlights how DML statements affect @@ROWCOUNT.
- States that DML statements may or may not send any rows to the client.
-
DECLARE CURSOR and FETCH:
- Specifies that DECLARE CURSOR and FETCH set @@ROWCOUNT value to 1.
-
EXECUTE Statements:
- Mentions that EXECUTE statements preserve the previous @@ROWCOUNT.
-
Other Statements:
- Lists statements like USE, SET
-
Natively Compiled Stored Procedures:
- Highlights how natively compiled stored procedures handle @@ROWCOUNT.
- Indicates that Transact-SQL statements inside such procedures do not set @@ROWCOUNT.
-
See Also:
- Provides references to related topics:
- System Functions (Transact-SQL)
- SET ROWCOUNT (Transact-SQL)
- Provides references to related topics:
This comprehensive overview covers the key concepts presented in the article related to @@ROWCOUNT and its usage in Transact-SQL, offering a solid foundation for database professionals and enthusiasts alike.
