I have a table which has a clustered index as below:
CREATE CLUSTERED INDEX [IX_MachineryId_DateRecorded] ON Machinery (MachineryId, DateRecorded)
I'm selecting based on the fields in the clustered index, plus one more:
SELECT DateRecorded, Latitude, Longitude FROM MachineryReading WHERE MachineryId = 2127 -- First key in the index AND DateRecorded > '2017-01-10' -- Second key in the index AND DateRecorded < '2017-10-16' -- Second key in the index AND FixStatus >= 2 -- Not a key, resulting in a scan ORDER BY DateRecorded
I would have expected this to result in a simple clustered index scan. However, looking at the live query statistics, and the actual execution plan, the majority of the query's execution time comes from sorting the results after the index scan. Why is the ordered data being sorted again?