>If I issue select pk from MyTable where MyNonUniqueColumn = 123
>
>the execution plan shows it uses an Index Seek on MyNonUniqueColumn index (is good)
>
>if instead I issue select pk,field2,field3,etc,etc. from MyTable where MyNonUniqueColumn = 123
>
>the execution plan now shows it's using a Clustered Index Scan on my PK index!
>
>What's going on here?
The optimizer decides that this is a less expensive plan, probably. Did you verify that statistics are up to date on this table? If it still will be bad plan, you can specify index hint in the query.
This is most likely irrelevant to your problem, but I want to share it anyway because of the very interesting last reply
http://social.msdn.microsoft.com/Forums/en/transactsql/thread/2ec66156-6225-49d7-a904-e055e1e733a6
If it's not broken, fix it until it is.
My Blog