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.

Enjoy! 🙂