Of course Paul is correct - it is picking what appears to be cheaper. I think the question here is why is the optimizer think the index is more expensive than full table scan to get single row from > 8 million rows and why when it uses the index (by forcing through a hint) does it use it incorrectly in 2016 (which accounts for the high cost)? — Saxman1 min ago
@JoeObbish I'm afraid the detail of cost numbers never interested me very much. Joe Chang did some good work in this area back in the day qdpma.com/cbo/SQLServerCostBasedOptimizer.html not sure how relevant that is to you, but you might find it interesting.
I do have an issue with the choice between apply/naive nested loop and the introduction of a row goal from the group by/top transform on the inner side of a LASJ, in general.
It does cause real problems, how ever one might criticize the simple demos.
