ROWCOUNT Behavior with Aggregate Functions

Introduction

I just wanted to share some analysis which I did while I was working on a stored procedure, created by some other developer.

Sometimes, you might find different error handling ways inside a stored procedure like some people use the error code to check the behavior of code or some other techniques. In this tip, I am going to discuss one of the techniques and its special behavior in a situation and why?

People use @@ROWCOUNT to check whether the data is coming in last select operation or if I would say in a better term, if someone wanted to check whether data is present or not for further processing, then they used to check @@ ROWCOUNT to verify whether any data is selected in last select operation or not.

Let’s take a look at the following example, suppose you are selecting the salary of an employee based on his/her age from employee table:

DECLARE @Salary NUMERIC (20,3)
SELECT @Salary=salary from tbl_EmployeeDetails
WHERE AGE<25
 
If (@@ROWCOUNT =0)
BEGIN
   RAISERROR ('Error', 0,1)
END

So you can see this is a very common approach which people used to handle error. Now, you might be thinking here, what's special in it? Ok, so let’s see the issue which you might not have noticed.

Suppose I wanted to get MAX salary of employee whose age is less than 25. So in this case, you can write logic as follows:

DECLARE @Salary NUMERIC (20,3)
SELECT @Salary=MAX (salary) from tbl_EmployeeDetails
WHERE AGE<25
If (@@ROWCOUNT =0)
BEGIN
   RAISERROR ('Error', 0,1)
END

So now again, what’s special here, it’s pretty much straight forward. Are you able to find the problem in the above code?

Yes, then super, you can stop reading the tip, if not then let’s see what the issue is. When you run this code, it will work fine until and unless there is no employee whose age less than 25 so suppose in this company, there is no employee whose age is less than 25. Now it will return 1 even if there is no employee’s data coming into select operation and it will not raise an error and the execution will continue further which could be the cause of another exception like divide by zero if you are using this salary in some calculation, etc.

Now the question which should pop up in your mind, what is the reason behind it. In a simple way, there is no difference, but when you try to see the execution plan, you can figure it out.

First, let’s understand the definition of @ROWCOUNT, it returns how many rows are affected/processed due to last operation.

As you can see the query plan, for the selective operation there is no data to select. Since number of rows affected/processed is zero, so @@ROWCOUNT value would be zero. But if you will see the query plan with aggregate function which I have used here as MAX, there are two operations first is same as before, there is no data for selection, but the second one is the MAX function which operates on a single row which has NULL. So when we check the @ROWCOUNT then you will be get 1 due to the aggregate function because last operation that is calculating MAX is performed on a single row (NULL);

Now let’s see the query execution plan for getting a more detailed view:

1. The below screenshot shows the actual execution plan for the select operation and as you can see actual number of rows 0 so @@ROWCOUNT is zero because no row processed in the last operation.

   

2. The below screenshot shows the execution plan of a select operation on the table before aggregate function executes:

   

3. Below screenshots represent 1 row has processed so @@ROWCOUNT would be 1:

   

So the next question is, how to resolve it, there are many ways, but I can think of, to check whether the data is coming as NULL or not, so you can put a condition like the following:

IF( @Salary IS NULL)
    BEGIN
     RAISE_ERROR()
   END