CODEWITHSUNDEEP
sqlsql-serverindexing
Kay
Kay

Reputation: 195

Why isn't SQL Server using my clustered index and doing a non-clustered index scan?

I have a patient table with a few columns, and a clustered index on column ID and a non-clustered index on column birth. 

create clustered index CI_patient on dbo.patient (ID)
create nonclustered index NCI_patient on dbo.patient (birth)

Here are my queries:

select * from patient 

select ID from patient

select birth from patient

Looking at the execution plan, the first query is 'clustered index scan' (which is understandable because the table is a clustered table), the third one is 'index scan nonclustered' (which is also understandable because this column has a nonclustered index)

My question is why the second one is 'index scan nonclustered'? This column suppose to have a clustered index, in this sense, should that be clustered index scan? Any thoughts on this?

query

Upvotes: 3

Views: 1551

Answers (1)

marc_s
marc_s

Reputation: 754368

Basically, your second query wants to get all ID values from the table (no WHERE clause or anything).

SQL Server can do this two ways:

  • clustered index scan - basically a full table scan to read all the data from all rows, and extract the ID from each row - would work, but it loads the WHOLE table, one by one
  • do a scan across the non-clustered index, because each non-clustered index also includes the clustering column(s) on its leaf level. Since this is a index that is much smaller than the full table, to do this, SQL Server will need to load fewer data pages and thus can provide the answer - all ID values from all rows - faster than when doing a full table scan (clustered index scan)

The cost-based optimizer in SQL Server just picks the more efficient route to get the answer to the question you've asked with your second query.

Upvotes: 6

Related Questions