What is @@ROWCOUNT and ROWCOUNT_BIG()?

@@ROWCOUNT

This is sql server global variable and returns the number rows affected by last statements.

Return Type:  INT

Syntax: - @@ROWCOUNT

Example:-

--declare variable to store the affected rows
DECLARE @NoOfRowAffected varchar(10)=null
UPDATE Employee   
SET Name = 'Alok Kumar Singh' 
WHERE Id = 1  
 
-- assigning number of affected rows
SET @NoOfRowAffected=@@ROWCOUNT
 
IF @@ROWCOUNT = 0  
PRINT 'Warning: No rows were updated';  
ELSE  
PRINT @NoOfRowAffected +' rows updated'

Remark Point:-

If you have a process that can affected greater than 4 Bytes data or more that 2 billion rows, then @@ROWCOUNT will throw an exception because its return type is INT.

In situation you can use ROWCOUNT_BIG sql built-in function same like @@ROWCOUNT.  ROWCOUNT_BIG return type is BIGINT 

Example:-

--declare variable to store the affected rows
DECLARE @NoOfRowAffected varchar(10)=null
UPDATE Employee   
SET Name = 'Alok Kumar Singh' 
WHERE Id = 1  

-- assigning number of affected rows
SET @NoOfRowAffected=ROWCOUNT_BIG()

IF ROWCOUNT_BIG() = 0  
PRINT 'Warning: No rows were updated';  
ELSE  
PRINT @NoOfRowAffected +' rows updated'

Difference between @@ROWCOUNT and ROWCOUNT_BIG

• @@ROWCOUNT is global variable while ROWCOUNT_BIG is sql build-in function
• @@ROWCOUNT Return type is INT while ROWCOUNT_BIG return type is BIGINT
• @ROWCOUNT store 4 Bytes value while ROWCOUNT_BIG can store 8 Bytes value