Another alternative that I currently have (I haven't measured/compared it) is to use a COMPUTE clause (if the client can cope with multiple result sets).

select a, b, c

from tableX

where a = 1

compute count(a)

The best part is that it works in .NET 1.1 and SQL 2000 (most of the systems I support) 🙂

Re-checking: the compute that I used was for obtaining the total of the result set (that was paginated). For pagination it's not so interesting because the number of rows retrieved is controlled by the client (the count should be for the whole table/view).

Select top 200 a, b, c

from tableX

where a = 1

compute sum(c)