Row count in big tables with DMV DM_DB_Partition_Stats and SP_SpaceUsed

I have seen people counting row by using COUNT or may be COUNT_BIG, if it is very big table, aggregate function. When you go for COUNT function, you will scan full table and it may take very big amount of time, especially when you are having VERY BIG table with few millions of rows. So .NET developer also uses same COUNT aggregate function in .NET front end application which is really very time consuming.

I just see one of my .NET developers was doing this for the table, having 150 million rows so I corrected him with following two ways and thought to share it with my blog readers.

Rather than scanning full table I always prefer to use either DMV or SP_SpaceUsed stored procedure. In my early days few years back, I was using SP_MSTABLESPACE but in SQL Server 2005+, SP_MSTABLESPACE is just for backward compatibility so I do not recommend using SP_MSTABLESPACE and promoting dm_db_partition_stats and SP_Spaceused.

Let us see  TSQL for each.

--Method1

SELECT

SUM(row_count) as TotRows

FROM

sys.dm_db_partition_stats

WHERE

object_name(object_id) = 'YourTableName'

AND (Index_Id = 1)

GO

--Method2

SP_Spaceused 'YourTableName'

GO

--Method3

SP_MSTABLESPACE 'YourTableName'

GO

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com