Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

James Serra's Blog

James is a big data and data warehousing technology specialist at Microsoft. He is a thought leader in the use and application of Big Data technologies, including MPP solutions involving hybrid technologies of relational data, Hadoop, and private and public cloud. 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 30 years of IT experience. James is a popular blogger ( and speaker, having presented at dozens of PASS events including the PASS Business Analytics conference and the PASS Summit. He is the author of the book “Reporting with Microsoft SQL Server 2012”. He received a Bachelor of Science degree in Computer Engineering from the University of Nevada-Las Vegas.

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:

SUM(row_count) as TotRows
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”.


No comments.

Leave a Comment

Please register or log in to leave a comment.