Poor performance-clustered index scan, non-clustered index scan, and table Scan

This article is applicable to the reading references of the attacker who has some in-depth knowledge about database indexes. We are easy to understand clustering index scanning and table scanning, but it is not easy to understand non-clustered index scanning. This is often confusing for beginners, the reason is that there is no need for non-clustered index scanning, because if the query results are not highly selective, clustering index scanning can be used in clustered index tables, why does a non-clustered index scan exist when a table is scanned? The reason for this problem is that we did not consider a situation, that is, if the query result is overwritten or contained by a field with a non-clustered index, at this time, the non-clustered index on the where condition field is not highly selective for the query results. So what kind of query plan will the query optimizer choose? 1. Non-clustered index search? This is definitely not acceptable, because the result set is not highly selective. 2. Clustered index scan (or table scan )? The answer is no, because the overhead of the first two is still relatively large. Why does our optimizer choose this non-clustered index? As long as the index is scanned once, the query does not need to access the data page at all, because the query result fields are overwritten or included by the index, which is much lower than the previous two. Haha!