SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


why query optimizer is using Non-clustered index for count(*) operation


why query optimizer is using Non-clustered index for count(*) operation

Author
Message
vigneshbeece
vigneshbeece
Old Hand
Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)

Group: General Forum Members
Points: 340 Visits: 260
why query optimizer is using Non-clustered index scan for "select count(*)" operation and Clustered Index scan for "select * from tablename" operation.
This table contains both clustered and non clustered Index. The column "file_No" is added to both the indexes as key value.
I have attached execution plan for reference.
Version:SQL version 2008R2


.
Attachments
Index doubt.jpg (29 views, 137.00 KB)
GilaMonster
GilaMonster
SSC Guru
SSC Guru (903K reputation)SSC Guru (903K reputation)SSC Guru (903K reputation)SSC Guru (903K reputation)SSC Guru (903K reputation)SSC Guru (903K reputation)SSC Guru (903K reputation)SSC Guru (903K reputation)

Group: General Forum Members
Points: 903568 Visits: 48754
Because a count(*) is asking how many rows are in the table, not the contents of the rows, and hence that can be answered with any index, as all non-filtered indexes have the same number of rows as the table.
Select * however is asking for all columns, all rows, and the fastest way to answer that is a table scan, which is what a clustered index scan is.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Steve Hall
Steve Hall
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16770 Visits: 13137

To expand on that slightly, the optimizer will use the narrowest index it can to achieve whatever it needs to, because there is less data to access.
The index it chose for the 'select count(*)' has an average of 9 bytes, whereas the clustered index is 8,345 bytes - why choose the larger index if the smaller one will achieve the same purpose?
When you use 'select *' you've asked for all of the columns and the non-clustered index can't fulfil that (at least, not alone - other work would be required) - but the clustered index can.


Steve Hall
Linkedin
Blog Site
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search