Index Seek on SQL Server

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?

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.

StackExchange Database Administrators Q#103767, answer score: 8