Why does performance degrade when using a non-indexed field in the SELECT clause?

Question

Consider these three queries:

select sampleno from sample
    where markupdate > '1/1/2010'

select sampleno, markupdate from sample
    where markupdate > '1/1/2010'

select sampleno, markuptime from sample
    where markupdate > '1/1/2010'

sampleno and markupdate are indexed fields (sampleno is the primary key)

markuptime is not indexed

Queries 1 and 2 take about 1 second to run (returning 237K rows). Query 3 is still running after 3 minutes.

Why would the inclusion of a non-indexed field in the SELECT clause cause such a performance degradation?

This is a SQL 6.5 database.

Answer 1

A table's data (basically: all columns) is stored in a clustered index. A clustered index is a binary tree that allows a binary search on the indexed column(s). It is special (clustered) in that it contains all other columns at the leaf level. Usually, the clustered index is also the primary key. In your case, it's:

(sampleno) include (markupdate, markuptime, ...)

A non-clustered index contains the indexed column(s) and (at the leaf level) the clustered index. When you use a non-clustered index, the database has to look up all the other columns in the clustered index. That process is called a lookup. In your case, the non-clustered index on (markupdate) is:

(markupdate) include (sampleno)

This index contains all data for a query on markupdate, sampleno. The technical term for such an index is a covering index. But when you add markuptime to the query, the index is no longer covering. It has to look up the value for markuptime in the clustered index. And lookups are expansive.

Only your third query requires lookups. And that's why your third query is slower.