SQL SERVER – @@RowCount Vs RowCount_Big()
@@RowCount and RowCount_Big returns the number of rows affected by the last executed statement in SQL Server. This statement could be a DML or a TSQL statement.
@@ROWCOUNT returns an integer value. So, Use this statement if the number of rows to affect is in between 0 and 2,147,483,647 range.
ROWCOUNT_BIG returns a big integer value. We can use this function; If the number of rows to affect is in between 0 and 9,223,372,036,854,775,807 range.
ExampleSELECT * FROM sys.tables SELECT RowCount_Big()
We can use these functions in the TSQL code to perform row count operation.
SELECT * FROM sys.objects WHERE type = ‘p’ AND name = ‘sp_creatediagram’
IF @@ROWCOUNT = 0
SELECT ‘Procedure Not found’
SELECT ‘Procedure name is valid’
Points to Remember
1. Transact-SQL statements that can set the value in @@ROWCOUNT are:
- Set @@ROWCOUNT is used to set the number of rows affected or read in DML statement.
- @@ROWCOUNT preserves the row count value of previous statement execution.
- @@ROWCOUNT can be used to resent the row count value to 0.
2. Statement that makes a simple assignment is always set the @@ROWCOUNT variable to 1. For instance, SET @local_variable, RETURN, READTEXT, and select without query statements such as SELECT GETDATE() or SELECT ‘Some Text‘.
3. Statements that are used to make an assignment operation in a SQL statement will set the @@ROWCOUNT value to the number of rows affected by the query, for example: SELECT @local_variable = c1 FROM t1.
4. Data manipulation language (DML) statements that can set the @@ROWCOUNT value to the number of rows affected by the query or it may assign different values based on the command as given below.
- DECLARE CURSOR and FETCH statement, set the @@ROWCOUNT value to 1.
- EXECUTE statement preserves the @@ROWCOUNT value of the previously executed statement.
- Statements such as USE, SET, DEALLOCATE CURSOR, CLOSE CURSOR, BEGIN TRANSACTION or COMMIT TRANSACTION resets the ROWCOUNT value to 0.
SET ROWCOUNT is a set statement that could affect the return value of ROWCOUNT function. Because, Set RowCount is used to restrict the number of rows to be processed by the query processor. So, use this in your application with clear understand of its behavior.
SET ROWCOUNT 3 — Set fixed number of rows to process
SELECT * FROM sys.objects
SET ROWCOUNT 0 — Reset the row count value to 0, means no row will be processed
After execution, above statements will return only 3 rows.
Do not get panic, open a new window and execute select * from sys.objects statement. It will return all rows from the system. Because, we have not restricted the rows to be processed by the query processor. So, make use of the SET ROWCOUNT properly.
I believe this blog post has given you some idea about @@RowCount and ROWCOUNT_BIG() T-SQL function. Thanks for reading.
We can also learn about Count Vs Big_Count here.