Paul White (9/5/2013)
When the nonclustered index is forced, an unfortunate side-effect results in two sorts being needed. As Erland mentioned, a general-purpose index optimization pre-sorts rows on ClaimID keys for the lookup into the clustered index. The set is then re-sorted on RecordDate later on.
I am stunned... Thanks Paul for stepping in and providing a great answer. I should have realised that it could not use the index directly because of the partitioning, but 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?
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.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]