T-SQL Tuesday #17: Applying APPLY
This month's T-SQL Tuesday is the brainchild of Matt Velic and was inspired by a quote from Adam Machanic on the importance of understanding the APPLY operator. It's a good trick to have up your sleeve as it can do some nifty things and really save you some keystrokes when used properly.
My first experience with APPLY occurred many moons ago when I was working with a system that required a pipe-delimited list of values. Fortunately whoever developed it long ago had the good sense to store the data properly (first normal form) in tables and only build the comma-delimited lists at query time. That being said, the queries used cursors to build the lists iteratively. This all worked fine, however the performance left a lot to be desired.
The solution I devised featured the CROSS APPLY operator as well as some trickery with XML to build the string. Here's what I came up with. When run against the AdventureWorks sample DB this will return a sorted and pipe-delimited list of each ProductID for every SalesOrderID.
1SELECT DISTINCT d.SalesOrderID, LEFT(ProdID.list, LEN(ProdID.list)-1) AS ProductList
2FROM Sales.SalesOrderDetail d
3CROSS APPLY (
4 SELECT CONVERT(VARCHAR(10), ProductID) + '|' AS [text()]
5 FROM Sales.SalesOrderDetail e
6 WHERE d.SalesOrderID = e.SalesOrderID
7 ORDER BY ProductID
8 FOR XML PATH('')
9) AS ProdID (list);CROSS APPLY does a great job of running a subquery that can build the desired strings. Of course there's way more interesting applications of APPLY than this, but this was how I first discovered how helpful it could be. I don't have the original cursor-laden code to do a comparison against, but it shouldn't be too much of a stretch to believe that the above code is way faster than iterating and looking for matches.
Happy Coding!