The underlying issue appears to be how merge join decides it wants columns ordered with a composite join predicate. https://dba.stackexchange.com/a/259853/3690

I looked at this right at the end of this answer and in the specific test I did 250 pages seemed to be a magic number.

I'm not claiming this...

I've been trying to find documentation on the exact page replacement policy myself without much success.

I did find the following quote though which might explain the difference in behaviour...

Thanks for your responses however I'm not using a loop either!

I am returning a ranked set of values and need to calculate the value of (Rank - 1)/RecordCount.

I have achieved this...

I'm not using a cursor!

I'm trying to get a sort of PercentRank functionality (though not exactly that)

in one select statement in SQL2005

OK:

I have a working model using something like

((ROW_NUMBER() OVER (ORDER BY UniqueKeyCombination...