Home > SQL Server, T-SQL > SQL SERVER – @@RowCount Vs RowCount_Big()

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.

Example

These two simple select statements are to print the current date and row count.
 
Select getdate()
Select @@ROWCOUNT

Output:

<<Date>>

1

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.

Example

SELECT  * FROM sys.tables
SELECT RowCount_Big()
 

We can use these functions in the TSQL code to perform row count operation.

Example

SELECT * FROM sys.objects WHERE type = ‘p’ AND name = ‘sp_creatediagram’
IF @@ROWCOUNT = 0
SELECT ‘Procedure Not found’
ELSE
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

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.

Example

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.

Advertisements
  1. December 3, 2014 at 12:37 am

    There are spelling mistake and also post is incomplete. Please update. I somewhat confused after reading it.!

    check below lines –
    1. [Statements such as USE, SET, DEALLOCATE CURSOR, CLOSE CURSOR, BEGIN TRANSACTION or COMMIT TRANSACTION reset the ROWCOUNT value to]
    — Incomplete

    2. [RownCount]
    — Spell mistake

  1. May 20, 2013 at 11:29 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: