Count number of rows without using any aggregate functions

Question

I need to count the number of rows in a table with a single SQL query.

That query should not contain any aggregate function like COUNT.

Answer 1

I'm a little surprised nobody has mentioned this yet:

EXEC sp_spaceused @objname = N'MyTable', @updateusage = 'TRUE' 

This will, among other things return the current count of records in the table.

This would also work in most cases:

SELECT rows 
FROM sys.partitions 
WHERE index_id IN (1,0) 
    AND object_id = OBJECT_ID(N'MyTable')