b5535084 (1/31/2012)
I can use that, for sure, but it changes nothing really. Similar I/O counts and CPU time actually goes up. I'm attaching the plans for the two versions...
Er, from those results:
TOP: CPU time = 1281 ms, elapsed time = 7777 ms, logical reads 223933 (ORDER_ITEM_DIM)
ROW_NUMBER: CPU time = 2000 ms, elapsed time = 15948 ms, logical reads 303216 (ORDER_ITEM_DIM)
TOP is better by every measure. The TOP plan looks better too: there's a (pointless) RID lookup in the ROW_NUMBER plan.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi