Erland Sommarskog (9/6/2013)
a question: shouldn't the optimizer be able to come with the same plan as for your rewritten query? That is, it could first to a TOP N sort on the small set, and then sort for the key lookup?
Yes, it could, and I would argue it should. Conor would say the optimizer can't consider every possible transformation, there haven't been enough customer requests for it, etc. etc.
More to the point, MIN and MAX are hard work on 2008+ partitioned indexes too, and that is an even more obvious optimization that is still missing.
By the by, the idea of manual rewrites to select keys first and then perform a small number of expensive lookups is a good general technique. I used the same basic idea for my SQL Server Central paging article:
http://www.sqlservercentral.com/articles/paging/69892/
I noticed that in the SQL 2005 plan it does something like this, but the first sort produces some 15000 rows and not 10000 rows, which I guess because there are rows with the same RecordDate.
The extra rows are due to nested loops prefetching, something I blogged about just a few days ago:
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi