I am in the process of choosing indexes on a table. I had a thought about non-clustered indexes on a table with a clustered index that I am not sure of the answer too. Maybe some guru out there knows the answer to this??
Say we have an Employee table as...........
With the following index........
- PK_EMPLOYEE on (ID) Clustered
Now say there is a query that runs frequently with ID and Dept in the WHERE clause. It seems that a composite index on ID and Dept would be appropriate. Right? This is where it got fuzzy for me. The leaf node in a non-clustered index (on a table that is clustered) contains the key value for the index and the key value in the clustered index. Instead of having a pointer to the data page there is a reference to the clustered index.
So here's my question.....Would it be equally as effective or even more effective to use a single column index on Dept than creating the composite index? The leaf node should still contain a reference to the key value in the clustered index, in this case ID. Does the query optimizer take into account that the key value for the clustered index exists in the non-clustered indexes leaf node?
Any thoughts, opinions, and random musings are welcome!!
"Keep Your Stick On the Ice" ..Red Green