CODEWITHSUNDEEP
sql-server
Katakam Suresh
Katakam Suresh

Reputation: 1

Clustered and Non-Clustered Index scan and seek

I have table with 4 columns (empId,name,age,location) and clustered index on empId (Primary key) and non clustered index on name.

When i tried to run a query like 

select empId from tblname

It does a nonclustered index scan on the table. This is even though I created the clustered index on empId. Why?

Upvotes: 0

Views: 114

Answers (1)

Sudhakar Singh
Sudhakar Singh

Reputation: 71

Whats wrong in it, its doing non cluster scan because sql engine is design in such a way. Let me explain you how its selecting your data and why its doing so.

You are selecting the emp id only. which is stored in two places in your DB. One place is your data page and the second one is your non cluster index page. As you have created a non cluster index on name and you also have a cluster index on emp id, so your non cluster index holds the emp id because you have cluster index on it.

If sql is not using cluster index, it will select the data from data page and in that case it need to read much more pages.

If sql is using non cluster index it need to read only cluster index page and imp is is already there in non cluster index page so the reads are low.

hope i am able to make you under stand.

Upvotes: 1

Related Questions