Dave Morrison (TSQLNinja), SQL geekery and some fun too

Main menu

Post navigation

Record counts the easy way

I’m always surprised that so many people that use SQL Server rely on the old COUNT(*) to retrieve basic full table record counts. Doing it this way basically makes sql server read the whole table to give you a count of the records, something it actually already knows!

Try doing this instead, replacing [YOUR TABLE NAME] with … well yeah, you get it

SELECT SUM (row_count)
FROM sys.dm_db_partition_stats
WHERE object_id=OBJECT_ID(‘[YOUR TABLE NAME]’)
AND (index_id=0 or index_id=1);

Do some comparisons of your own, do a COUNT(*) on a table and then do this.