>The calculus might be the same, since they are doing much the same thing. However, a cross apply force an INNER JOIN. Rows from the left table that do not have a match in the right table expression are excluded from the result. The OUTER APPLY is a better match.
>
>Personally I'd like to avoid anything that is not part of the SQL92 standard. Many of the new features can only be used in SQL of certain vendors or even versions. Most of the things people do with those new features can be done in another way just as easily.
>
>We have a large product on which until recently had SQL 2000 as our minimum requirement, so we could not use those new features and you'll have to learn to do without them. Our new version of the product is taking SQL 2005 as the minimum requirement, so finaly we can use features like RANK() OVER ... and recursive queries. However, appart from RANK(), I've not been using any of those features, because in most cases there is a good alternative in good old plain SQL92 form.
CROSS APPLY simulates an INNER JOIN, yes....and OUTER APPLY simulates an OUTER JOIN. It winds up using an index spool. Sometimes it can help with performance (can limit the set being joined beforehand) and sometimes can be worse.
I once read that the best way to think of a CROSS APPLY is that it's basically a CROSS JOIN that's correlated to a subquery or a table-valued function. I know some people in large reporting applications who love it, and others who would never touch it.
I agree, of all the new T-SQL features in 2005, the ranking features were arguably the most valuable. GROUPING SETS are nice, PIVOT is nice, and I've always liked CROSS APPLY, but yes, RANK and DENSE_RANK were the toughest to create a workaround.
This has been a good discussion because of the different perspectives - seeing things both from the viewpoint of the "latest and greatest" and the viewpoint of compatibility with older versions.