Apr 122011
 

T-SQL TuesdayThis month’s T-SQL Tuesday is the brainchild of Matt Velic (blog | twitter) and was inspired by a quote from Adam Machanic (blog | twitter) 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.

SELECT DISTINCT d.SalesOrderID, LEFT(ProdID.list, LEN(ProdID.list)-1) AS ProductList
FROM Sales.SalesOrderDetail d
CROSS APPLY (
	SELECT CONVERT(VARCHAR(10), ProductID) + '|' AS [text()]
	FROM Sales.SalesOrderDetail e
	WHERE d.SalesOrderID = e.SalesOrderID
	ORDER BY ProductID
	FOR XML PATH('')
) 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!

  5 Responses to “T-SQL Tuesday #17: Applying APPLY”

  1. Very nice! I’m happy to see this among the handful of posts that don’t have to do with cached plans!

  2. Thanks for writing, Bob!

  3. […] Bob Pusateri ( Blog | @SQLBob ) shared a small script that can help with delimiting a list of values. […]

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)