SQL Server Performance Forum – Threads Archive

Index Selectivity

Hi All I have some doubts on Index Selectivity . Suppose I have a table Orders with two columns OrderId and EmployeeId and some other columns .
I fire a query SELECT OrderID FROM Orders WHERE EmployeeID = 10
This Query return 70% of the rows in the table
Now Consider two cases 1) There is a non-clustered index on EmployeeId , Since 70% of the rows are being returned the non-clustered index will not be used and the optimiser will go for a table scan 2) But suppose that there is a clustered index on OrderId , so this value will be stored as a Bookmark value in non-clustered index of EmployeeId .
Now in this case will it go for a table scan or still use the non-clustered index . What I feel is that it will go for non-clustered index of EmployeeID because the values of OrderId is lying there. Am I correct ? If yes why? If not why?
[B)]

---

With a clustered index on OrderID and a non clustered on EmployeeId it will do a non-clustered index seek.
Run it in QA, look at the execution plan and you will all the details. Bambola.

---

I agree with Bambola on this. Gaurav

---

the only reason the execution plan will use a table or index scan instead of an index seek when a SARG is specified and there is an index on the SARG is due to the cost of the bookmark lookup.
Hence if you have a covered index, whether the other columns are explicitly specified or come along as part of the clustered index, there are no bookmark lookups invovled and the index seek plan will always cost less than a scan if .

---

]]>