Just read this and found it very interesting. The following are 2 queries to get rowcount on a database table:
Query 1 (the one everyone knows):
SELECT COUNT(RowID) AS [RowCount]
FROM elig_Inbox
Query 2 (the interesting one):
SELECT st.row_count AS [RowCount]
FROM sys.dm_db_partition_stats st
WHERE index_id < 2
and OBJECT_NAME(OBJECT_ID)='elig_Inbox'
What's really interesting to me is that once you look at the execution plan of the 2 queries, you'd find the improvement in performance.
In real life, we probably don't care about it as often as we should. For example, both queries, executed against a table of 260k + rows, returned result within a second.
However, one drawback I can think of immediately, is that Query 2 does not have the Table Hints option. Most of the time we do RowCount because we need some logic done and we need to lock the table while we do the count. I guess the way around that would be to manually initiate a lock before Query 2, then release it after. It will be interesting to see whether that puts the 2 query performance back on par.