A CROSS APPLY in SQL Server pairs each row of a table with the results of a table-valued function (TVF) or subquery, acting like a join that evaluates the right-side expression for every left-side row. Unlike a CROSS JOIN, it only includes rows where the right-side expression returns data, making it ideal for dynamic, row-specific operations like calling a function (e.g., SELECT * FROM Orders CROSS APPLY dbo.GetOrderDetails(Orders.OrderID)). It’s more flexible than correlated subqueries and distinct from OUTER APPLY, which preserves left-side rows even when no right-side results exist.
It shines in scenarios like splitting strings or fetching related data per row, but its per-row evaluation can hurt performance with large datasets or complex expressions. If the left table has many rows and the TVF or subquery is resource-heavy—lacking optimization or indexing—it can lead to excessive CPU, memory, or I/O usage, slowing queries significantly. This row-by-row processing can make it less efficient than precomputed joins or simpler alternatives.
To mitigate performance issues, ensure the TVF or subquery is optimized (e.g., indexed properly), limit the left table’s rows with a WHERE clause beforehand, or consider materializing results into a temporary table first. Poorly designed CROSS APPLY usage can turn a query into a bottleneck, especially in high-volume environments, so always check the execution plan to identify costly operations like scans or repeated function calls.
Need help with this or anything relating to SQL Server? The team at Stedman Solutions can help. Find out how with a free no risk 30 minute consultation with Steve Stedman.
Struggling with slow SQL queries? Discover how our query tuning services can help: https://stedmansolutions.com/sql-server-query-tuning/
More from Stedman Solutions:
Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!