Unanswered: desperately seeking a purpose for CROSS APPLY
I'm a little late on tackling the CROSS APPLY & OUTER APPLY functionality, but I've read several articles on it and I'm still confused. This functionality is intended to allow table functions with parameters to be executed once per record and then joined to the results. What I don't understand is why this would be used instead of ANSI SQL with a view (either inline or stored).
In the article I just read, the example of cross apply that couldn't be achieved with an inner join was:
SELECT * FROM Department D
CROSS APPLY dbo.fn_GetAllEmployeeOfADepartment(D.DepartmentID)
However, the following inner join accomplishes the same result:
select * from department d
inner join employee e on d.departmentid=e.departmentid
The ANSI SQL has the same performance as the CROSS APPLY, but I would argue it's more readable because code isn't blackboxed in a function. I'm sure table-functions could get more complicated with dozens of parameters, but it wouldn't seem relevant since those parameters would just be grandfathered into ANSI SQL as segments of your WHERE clause.
Has anyone seen any example with a user-defined table function (NOT one of Microsoft's DMF's) where CROSS APPLY provides advantages over an ANSI join?
I'm starting to wonder if the only reason CROSS APPLY was created is because table valued functions are severely limited without it - and then the next logical question would be: are table functions necessary? I thought they sounded like a great idea when they were introduced, but I've yet to use one in production.
Last edited by onansalad; 09-02-11 at 10:52.