Question :
Sorry folks if this sounds obvious. But does index seek always work on index pages not data pages? If so, which I think it is, it can only be used on its own if the output list are the same as index fields/covering . As a result, if a non-index field is needed for the output , it is neither not used by a query engine at all or used with Key/RId lookup which does not have a good performance. So I can conclude that index seek is only beneficial if the data is already covered by the index. is that correct?
Answer :
You are correct in your description but the conclusion is not correct. It may still be faster to use the index anyway even if it has to go off to the table for the rest of it. The data pages contain many fewer rows than the index so there’s lots of extra I/O when scanning. Generally the index will tend to give you benefits if it selects few rows even if it’s not a great fit.
Even if the data is not stored on the leaf level, the index will provide your database engine with a very exact location in the data pages where to look for the results.
I.e. instead of browsing all of your table’s data pages for relevant data, your index seek first browses fewer and smaller pages, ideally with a hierarchy (so that it browses even fewer).
As a result you get a number of locators, which will admittedly have to be looked up from the complete set of (unsorted) data pages, but since you’re looking up a specific page and location every time, it’s still reasonably fast.