So here it is, the first one of these not-so-original knowledge, but I didn't know until I needed it today.
In SQL Server it may take a long time to simply return a total record count using select count (*) from YourTable as the engine may decide to perform a scan instead of a seek.
Among other places this page helped me with a solution that retrieves the record count of a table instantaneously:
SQL 2005 or later
select sum (spart.rows)
from sys.partitions spart
where spart.object_id = object_id('YourTable')
and spart.index_id < 2
where id = object_id('YourTable')