@@Rowcount and Aggregative functions

Please note the following,

When using aggregative function(s) in a SELECT that not returns any row
you will always get one row in the result set and @@RowCount will be always
be equal to 1.

Let`s see an example:

CREATE TABLE TestTable (RowID INT NOT NULL)
GO

-- Fetch rows without aggregative function in the Select
SELECT * FROM TestTable

SELECT @@ROWCOUNT as RowsCount

href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgNPibrCUL3vvrjwbyuRSkltpp-fgGx0m7cRAZbz8j2CgavGDOnVWjfbxPtX7RN_nQoUAzXA6zhTKJA7-ex5fnIeU-LQcQ9kv9_JvWBQWSzvusdNt2tZDa_OUr0oJS_iAFHYRvglJv3sbk/s1600/RowCount_noAggFunc.JPG" imageanchor="1" style="clear: left; cssfloat: left; float: left; margin-bottom: 1em; margin-right: 1em;">














As you see, the result of @@ROWCOUNT  is 0 as expected,
Now let`s see what will happened if we add a aggregative function to the
SELECT, for example, COUNT/AVG and etc.

CREATE TABLE TestTable (RowID INT NOT NULL)

GO

-- Fetch rows with aggregative function in the Select
SELECT AVG(RowID) FROM TestTable
SELECT @@ROWCOUNT as RowsCount

 
 
 
 
The @@RowCount is 1, even the table is empty.
So, when writing logic that depends on the result of @@RowCount,
keep in mind the case described above.

Tweet