How to get row count of a table in SQL Server quickly? Easist way, of course, is “SELECT COUNT(*) FROM <Table Name>”, most of you would say. However, there is another way, even easier and faster, that is “sp_spaceused <Table Name>”. It is shorter, and more importantly, it returns instantaneously when you try to get information of a huge table (millions of lines).
Experiment on a table with 2.8 million rows shows that time spent with COUNT(*) method is 3 seconds. Actual time taken is dependent on your hardware environment.
A good article explaining how it works: http://www.sqlservercentral.com/articles/T-SQL/67624/