A quicker way than using count(*)

A quick little tip I found a couple of years ago. Ever use the count(*) syntax and it takes forever to count all the rows in a table? I had this problem when I would sometimes run counts on a bunch of tables to check that my replication was working correctly. Here is a much, much quicker way using the DMV sys.dm_db_partition_stats:

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

Running the count(*) on one of my large tables took 33 seconds. The above statement took 1 second.

If you want to do a record count on all tables in a database:

SELECT OBJECT_NAME(OBJECT_ID) TableName, st.row_count
FROM sys.dm_db_partition_stats st
WHERE index_id < 2
ORDER BY st.row_count DESC

Another little known way to get table counts quickly is in SSMS click View -> Object Explorer Details, then click on the “Tables” folder for any database. By default you will see all the table names, but not the row count. To get that, right-click on a column header and select “Row Count”.

About James Serra

James is a big data and data warehousing solution architect at Microsoft. Previously he was an independent consultant working as a Data Warehouse/Business Intelligence architect and developer. He is a prior SQL Server MVP with over 25 years of IT experience.
This entry was posted in Quick Tip, SQL Server, SQLServerPedia Syndication. Bookmark the permalink.

One Response to A quicker way than using count(*)

  1. Ivan Roguljić says:

    Hi!

    this is an interesting tip although I generally use:
    sp_spaceused ‘tablename’
    – gives the same result with similar performance but with less code.

Leave a Reply

Your email address will not be published. Required fields are marked *