The fastest query for this purpose is:
SELECT rows
FROM sysindexes
WHERE id = OBJECT_ID (‘TableName’)
AND indid < 2
With this query, SQL does not have to do a full table scan or an index scan to find out the number of rows in a table. The rows column in the sysindexes table keeps the current rowcount dynamically for the clustered index. If table has a clustered index then indid = 1. If no clustered index exists then indid = 0 for the table. The statement
SELECT COUNT(*) FROM TableName
requires a full table scan or full clustered index scan to determine the total number of rows.