Tuesday, June 7, 2011

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
















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.

No comments:

Post a Comment