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.
select count (*) from tablename with (nolock)
ReplyDeletealso 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
But the whole point of the above query is to eliminate the index scan .
ReplyDelete