What is the main purpose of using CROSS APPLY?
I have read (vaguely, through posts on the Internet) that
cross apply can be more efficient when selecting over large data sets if you are partitioning. (Paging comes to mind)
I also know that
CROSS APPLY doesn't require a UDF as the right-table.
INNER JOIN queries (one-to-many relationships), I could rewrite them to use
CROSS APPLY, but they always give me equivalent execution plans.
Can anyone give me a good example of when
CROSS APPLY makes a difference in those cases where
INNER JOIN will work as well?
Here's a trivial example, where the execution plans are exactly the same. (Show me one where they differ and where
cross apply is faster/more efficient)
create table Company ( companyId int identity(1,1) , companyName varchar(100) , zipcode varchar(10) , constraint PK_Company primary key (companyId) ) GO create table Person ( personId int identity(1,1) , personName varchar(100) , companyId int , constraint FK_Person_CompanyId foreign key (companyId) references dbo.Company(companyId) , constraint PK_Person primary key (personId) ) GO insert Company select 'ABC Company', '19808' union select 'XYZ Company', '08534' union select '123 Company', '10016' insert Person select 'Alan', 1 union select 'Bobby', 1 union select 'Chris', 1 union select 'Xavier', 2 union select 'Yoshi', 2 union select 'Zambrano', 2 union select 'Player 1', 3 union select 'Player 2', 3 union select 'Player 3', 3 /* using CROSS APPLY */ select * from Person p cross apply ( select * from Company c where p.companyid = c.companyId ) Czip /* the equivalent query using INNER JOIN */ select * from Person p inner join Company c on p.companyid = c.companyId