When we use select count(), the query processor will use scan on the index with the smallest number of pages to find the result of select().
SQL Server Tips · · · ·
I had to go to Paul's link to confirm the typo in this post. I believe you want to refer to COUNT(*), not SELECT(*)
For COUNT(*), scanning the index makes sense. In fact, it is one of the most accurate methods to get the count of records in a given table. You may be interested to read more about it in my post: Measuring the number of rows in a table – are there any alternatives to COUNT(*)?
Nakul, great post!!! really enjoyed it. Thats my bad select(*)...I will edit it...