This is a continuation of my previous post about paging in SQL Server. When it comes to paging, you would naturally want to know the total number of rows satisfying, so you can display some nice, useful information to your end-users.
You would think, well, it’s just a count, and a simple query like this would be enough:
SELECT COUNT(Id) FROM MySecretTable
There should be nothing to worry about, right? Actually, there is.
Let’s get back to the example in previous post – we have to count the total number of orders in that big table.
SELECT COUNT(ObjectId) FROM OrderGroup_PurchaseOrder
Because ObjectId is the clustered index of OrderGroup_PurchaseOrder, I did expect it to be use that index and be pretty fast. But does it? To my surprises, no.