@@RowCount Vs RowCount_Big()

ROWCOUNT_BIG Function :

ROWCOUNT_BIG function returns number of rows affected by last executed statement. It works same as @@ROWCOUNT except the return type differs.  

This variable is set to 0 by any statement that does not return rows, such as an IF statement.

If the table has more than 2 billion rows, use ROWCOUNT_BIG().

returns a big integer value. If the number of rows to affect is in between 0 and 9,223,372,036,854,775,807

Return type of ROWCOUNT_BIG function is a bigint.

select orderNr from orp

select RowCount_Big()

@@RowCount :

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

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.

1. 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‘.
2. 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.
3. 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.
4. DECLARE CURSOR and FETCH set the @@ROWCOUNT value to 1.
5. EXECUTE statements preserve the previous @@ROWCOUNT.
6. Statements such as USE, SET, DEALLOCATE CURSOR, CLOSE CURSOR, BEGIN TRANSACTION

select orderNr from orp

select @@rowcount

SET Rowcount :

Set RowCount is used to restrict the  number of rows to process by the query processor.

Example:

select * from A1

Output:

Id

1

2

NULL

4

set rowcount 2 -- Set no of rows to process
select * from A1

Output:

id
1
2

set rowcount 0 -- Reset the row count value