CROSS APPLY Fundamentals: Part 1

Question

CROSS APPLY Fundamentals: Part 1

Imran2629

SSC Journeyman

Points: 83
More actions
July 27, 2026 at 12:00 am

#4884196

Comments posted to this topic are about the item CROSS APPLY Fundamentals: Part 1

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

SoCal_DBD Hall of Fame Points: 3413 More actions · Answer 1

Curious of when others use CROSS APPLY vs ROW_NUMBER() to get the top N. Of course, many things may come into play when determining which to use and we need to determine what is best for our own use case. When testing in my environment, CROSS APPLY had double the scans and reads on my detail table (names obfuscated):

-- CROSS APPLY:

Table 'CoreTable'. Scan count 3, logical reads 6098, physical reads 0, page server reads 0, read-ahead reads 1226, page server read-ahead reads 0, lob logical reads 138823, lob physical reads 7, lob page server reads 0, lob read-ahead reads 442389, lob page server read-ahead reads 0.
Table 'CoreTable'. Segment reads 234, segment skipped 53.
Table 'Worktable'. Scan count 4, logical reads 10, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'DetailTable'. Scan count 28, logical reads 252958, physical reads 0, page server reads 0, read-ahead reads 121546, page server read-ahead reads 0, lob logical reads 4938481, lob physical reads 2730, lob page server reads 0, lob read-ahead reads 15867171, lob page server read-ahead reads 0.
Table 'DetailTable'. Segment reads 1610, segment skipped 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

-- Using ROW_NUMBER:

Table 'CoreTable'. Scan count 3, logical reads 6098, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 138772, lob physical reads 2, lob page server reads 0, lob read-ahead reads 442240, lob page server read-ahead reads 0.
Table 'CoreTable'. Segment reads 234, segment skipped 53.
Table 'DetailTable'. Scan count 15, logical reads 126479, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 3574586, lob physical reads 2643, lob page server reads 0, lob read-ahead reads 10419262, lob page server read-ahead reads 0.
Table 'DetailTable'. Segment reads 805, segment skipped 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

My knee jerk reaction would have been to use:

; WITH CustomerOrders AS
(SELECT
    c.CustomerID,
    c.CustomerName,
    o.OrderID,
    o.OrderDate,
    o.ProductID,
    o.Quantity,
    RowNum = ROW_NUMBER() OVER (PARTITION BY c.CustomerID ORDER BY o.OrderDate DESC, o.OrderID DESC)
FROM Customers c
INNER JOIN Orders o
    ON c.CustomerID = o.CustomerID
)

SELECT 
    CustomerID,
    CustomerName,
    OrderID,
    OrderDate,
    ProductID,
    Quantity
FROM CustomerOrders
WHERE RowNum = 1;

My "CoreTable" has 135818076 rows.

My "DetailTable" has 752725863 rows.

SoCal_DBD Hall of Fame Points: 3413 More actions · Answer 2

SoCal_DBD

Hall of Fame

Points: 3413

July 27, 2026 at 4:42 pm

#4907738

Got an assist from Claude which helped:

CROSSAPPLY

MMartin1 One Orange Chip Points: 27884 More actions · Answer 3

CROSS APPLY as illustrated here will perform so much better if you give the 'TOP' query a covering index on the Orders table.

----------------------------------------------------