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.
Happy Coding!