I have a table with a single Non clustered Index on say col1 and another column which is not a part of any index ex col2.Now when I query the table with this querySelect col2 from table where col1='Some Value'
I get a straight Clustered Index Scan which is expected.But the same query when written like thisSelect col2 from table where col1 like 'Some Value'
The optimiser uses a Index seek but with a Key Lookup.Can anyone explain this behaviour?From what I think is it that for the each row the like clause touches it uses a keylookup to get the value of col2.If thats the case then why is optmizer using a seek on col1 to traverse through all the rows ?Is it right or am I missing something?PBUH
Two different plans
Posted - 2010-12-11 : 07:56:23
