Anyone who has worked with a database for even a short amount of time has undoubtedly checked the number of rows in a table with a statement akin to this:
SELECT COUNT(*) FROM dbo.TableName
It’s quick and easy, but is expensive in terms of logical reads. Also, things get complicated when you want to check the numbers of rows for all user tables in an entire database – you might think about resorting to cursors and dynamic SQL.
Fortunately, SQL Server 2005 shipped with a whole slew of Dynamic Management Views that make monitoring your database much simpler. With a quick query, we can view the rowcount for all tables in a database – and no table scan required.
Continue reading »