If those two queries produce identical results, it is just luck. Your original queries had (rn = 1) ordered by order_item_id ascending for the JOIN option (which would give the row with the lowest order_item_id). The APPLY form used MAX(order_item_id) which will produce the highest order_item_id of course. Your new query just uses TOP (1) without an ORDER BY - so which row you get is down to chance. The ROW_NUMBER in the apply does nothing, by the way. To answer your implied question about the worktable reads - these are related to the on-the-fly index SQL Server is creating for you each time the query runs (the Eager Index Spool in the query plan). This is an indication that you are missing a real index that would be helpful to the APPLY query. Based on the information provided, this is how I would write the APPLY query, and includes the definition of the index you should create to get best performance from it:

SELECT

nco.ORDER_ID,

nci.ORDER_ITEM_ID

FROM OR_NCCore.dbo.Order_Dim AS nco

JOIN OR_NCCore.dbo.Status_Dim AS s

ON nco.STATUS_ID = S.STATUS_ID

CROSS APPLY

(

SELECT TOP (1)

nci.ORDER_ITEM_ID

FROM OR_NCCore.dbo.ORDER_ITEM_DIM AS nci

WHERE

nci.ORDER_ID = nco.ORDER_ID

ORDER BY

nci.ORDER_ITEM_ID ASC

) AS nci;

-- Optimal index for this query:

CREATE INDEX x

ON OR_NCCore.dbo.ORDER_ITEM_DIM

(ORDER_ID, ORDER_ITEM_ID);