Don't laugh, things move slowly at my place of work, so we are still using
SQL Server 7.0 SP4...
Anyway I tried a query where the WHERE clause was a particular value
SELECT min(ProgrammeID),max(ProgrammeID) from Programme where [date]=37285
ProgrammeID is an integer and PK and clustered.
However there is also a unique index on [Date],... other field entries.
Incredibly, the PK was used to look up [Date]. That involves a whole PK scan
checking on [Date]. Channging this to
SELECT min(ProgrammeID),max(ProgrammeID) from Programme with
(index(IX_ChannelDateTime)) where [date]=37400
meant an instant response.
Seems that optimiser is not that bright. If the ...