I've used Cross and Outer Apply a few times very successfully. They work better with sub-queries than with UDFs. Even a single-select UDF has a performance hit of a few milliseconds at least, compared to having the query directly in the calling routine.
For something like "top 3 of each", row_number is generally significantly faster. But for more complex queries, Apply works just fine.
Just make sure the outer query has as few rows as possible, because it is essentially RBAR at that point. Can still be fast, but keep it to only what you actually need.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon