Does COUNT(*) always return a result?

Yes, the return value of the "count" function itself is always a non-null integer, without exception.

Having said that, you may be more interested in the return value of your query, which is actually a result set. If that's the case, then you simply have to consider whether your query includes a "group by" clause.

A non-grouped query with aggregate functions like

select count(*), sum(*), max(*), min(*) from table

produces a result set with a single record containing the return value(s) of the aggregate function(s). In this case, your result set will always have exactly one record, with columns containing the return values of whatever aggregate functions you included. Within this lone record, the return value of count will always be a non-null integer, while the return values of the other aggregate functions such as min, max, and sum may be null. In the above example, your returned record may be "zero, null, null, null", but will never be "null,null,null,null", since count never returns null. So if you're calling only "count", then that one column in that one record returned will always contain the non-null return value of count; hence you can rest assured that the scalar return value of your count query will always be a non-null integer.

On the other hand, a grouped query with aggregate functions like

select count(*), sum(*), max(*), min(*) from table group by column

produces a result set with zero or more records where each record contains the return value of the aggregate function(s) for each group identified. In this case, if there are no groups identified, there are no groups for the aggregate functions to run on, the functions are never run, and the "query return value" will be an empty set with no records at all.

Test it out on a table with no records to see the results:

create table #t (c int);
select COUNT(c), MIN(c), MAX(c), SUM(c) from #t; --returns one record {0, null, null, null}
select COUNT(c), MIN(c), MAX(c), SUM(c) from #t group by c; --returns no records
drop table #t;

So whether you're calling count, min, max, or sum, it is important to understand not only the return values of individual aggregate functions, but also the "return value" of the query itself, and what that result set contains.

Aggregate function Count() always returns value

Yes, because it's an aggregate and returns zero. Unless you add GROUP BY in which case no result because there is no group...

MAX/SUM etc would return NULL unless you add GROUP BY then no rows. Only COUNT returns a number for no results

Edit, a bit late: SUM would return NULL like MAX

Edit, May 2013: this applies to all main RDBMS. I guess as per the ANSI standard