Erik Darling is in the middle of a back-to-basics series on performance tuning:
And you see, once you set up a query to return the TOP N rows, there’s an expectation that users get to choose the order they start seeing rows in. As long as we stick to columns whose ordering is supported by an index, things will be pretty stable.
Once we go outside that, a TOP can be rough on a query.
Read on for an example of what happens when that type of thing goes wrong.