Tuesday, September 29, 2009

Better way of finding the no of rows in a table

This is my first blog post and I wanted to keep it short and simple.

We often get requests or we ourselves would like to know the no of rows in a particular table. Doing a Count(*) on the table is not the right way to find the no of rows as it will always do an index scan or a table scan and if the table is large then it would take a lot of time .

The best way to find the count is by using the sys.partitions table.


  1. select count (*) from tablename with (nolock)

    also helps a lot in improving the performance. It does not place a lock while fetching the number of rows thereby not blocking any transactions or connections that need to access or place a lock on the table

  2. But the whole point of the above query is to eliminate the index scan .