Rowcount for Large Tables July 24, 2008Posted by furrukhbaig in DMV's, Performance, SQL Server 2005, SQL Server 2005 features, Tips, TSQL.
Tags: large tables, rowcount, rowcount scan, sys.dm_db_partition_stats
Ever wondered why simple statements like SELECT COUNT(*) FROM [Table_Name] takes forever to return row count on large tables? Its because it uses full table scan to count number of rows. The instant way to get the row count on any table is to query new Dynamic Management Views (DMV) in SQL Server 2005 sys.dm_db_partition_stats. DMV contains row count and page counts for any table including all the partitions. Please note even you did not create partition for your table, your table still going to be created on single default partition so this will include all the tables.
Following are some useful queries that will return row count almost instantly. In SQL Server 2000 sysindexes system table is used to get the number of rows for large table to avoid full table scan.
Comments are welcome.
— All the tables having ZERO rows
— compatable with partitioned tables
Table_Name = object_name(object_id),
Total_Rows = SUM(st.row_count)
SUM(st.row_count) = 0
— Row Count without doing full table Scan
— This will include provide total number of rows in all partition (if table is partitioned)
object_name(object_id) = ‘Your_Table_Name_Goes_Here’
AND (index_id < 2) — Only Heap or clustered index