T-SQL Tuesday #17: Applying APPLY
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.