Advertisement

Latest Post

Friday, 9 December 2016

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

Government Jobs