>>>Kevin,
>>>
>>>Just an example how to do this in SQL 2005 without CTE's
>>
>>
>>Wow, thanks, that actually performs better than anything I'd seen prior to the new windowing features in SQL 2012. My first inclination is to use CROSS APPLY though there's additional cost to that.
>>
>>
>>On the CTEs - in absence of recursion, they offer no functional benefit - they are syntax sugar for derived table subqueries (though some people prefer sugar!).
>>
>>
>>Thanks, great answers. Wrap-up tonight, have to get back to work (or my power-nap, whichever I decide) :)
>
>I think CROSS APPLY and the Subquery shown by Walter are actually equivalent. I prefer CROSS APPLY as we can have more than 1 column.
>
>Brad Schulz had a blog explaining that CROSS APPLY and subquery is the same.

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.

Walter,







is