November 21, 2017 at 11:22 pm
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
.
November 22, 2017 at 12:39 am
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
November 22, 2017 at 1:46 am
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy