Here are some methods to return the row count from a table: –
SELECT row_count = SUM(row_count) FROM sys.dm_db_partition_stats WHERE [object_id] = OBJECT_ID(N'Sales.SalesOrderHeader') AND index_id < 2; --0 = Heap; 1 = Clustered index.
SELECT row_count = SUM(rows) FROM sys.partitions WHERE [object_id] = OBJECT_ID(N'Sales.SalesOrderHeader') AND index_id < 2; --0 = Heap; 1 = Clustered index.
EXECUTE sys.sp_spaceused 'Sales.SalesOrderHeader';
EXECUTE dbo.sp_DataProfile 'Sales.SalesOrderHeader',0;
SELECT COUNT(*) FROM Sales.SalesOrderHeader;
A direct count against the table will give an accurate row count but will hold locks potentially blocking other connections. For most scenarios, sp_spaceused or a DMV query is accurate enough. It is typically not accurate if other connections are modifying data and if that is the case, a direct table count might give you an accurate count that will soon be stale anyway.