Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

James Serra's Blog

James is currently a Senior Business Intelligence Architect/Developer and has over 20 years of IT experience. James started his career as a software developer, then became a DBA 12 years ago, and for the last five years he has been working extensively with Business Intelligence using the SQL Server BI stack (SSAS, SSRS, and SSIS). James has been at times a permanent employee, consultant, contractor, and owner of his own business. All these experiences along with continuous learning has helped James to develop many successful data warehouse and BI projects. James has earned the MCITP Business Developer 2008, MCITP Database Administrator 2008, and MCITP Database Developer 2008, and has a Bachelor of Science degree in Computer Engineering. His blog is at .

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”.

Comments

No comments.

Leave a Comment

Please register or log in to leave a comment.