Count Rows In A Large Table in SQL Server

Even in smaller environments the DBA will run into large databases and very large tables. This can be due to neglect, bad table design or just a recent change with unexpected results. When dealing with these large tables we often need the fastest way to get the table row count. This is a simple SQL statement I use to find the current table row size quickly if the table is not partitioned (yet).

SELECT  SUM(row_count)
FROM    sys.dm_db_partition_stats
WHERE   object_name(object_id) = 'myTableName' AND (index_id < 2)

sys.dm_db_partition_stats

The Microsoft page describes the dm_db_partition_stats dynamic management view as “Returns page and row-count information for every partition in the current database.” To see the partitions for all objects you can use the sys.partition table.

SELECT object_name(object_id), * FROM sys.partitions

Then use the object_id to target only the tables you are interested in.

SELECT object_name(object_id), * FROM sys.partitions where object_id = 234234523456

In a live production environment you can re-run this statement to see that the [rows] field increases each time the table is written to. This means that the row count from our statement is fast but not necessarily accurate as the data may be changing.
This is great for the DBA to automate, estimate and report on sizes and growth.

SELECT COUNT(*) Taking a long time

The most common T-SQL statement used to get the number of rows is COUNT(*). This statement takes a longer time to complete because it is literally counting all of the records in the table. Using this statement you may be able to speed things up by adding an index but this could be counter productive due to the time it takes to create the the index on a huge table.