Blog Post

T-SQL Tuesday #17: Applying APPLY

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.

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!

Related Posts

By Bob Pusateri

Bob Pusateri is a Microsoft Certified Master of SQL Server, speaker, blogger, volunteer, and author who loves finding new and exciting ways to break and fix things. He works as a consultant and architect, specializing in data, virtualization, and cloud technologies.

5 Comments

Leave a Reply

Your email address will not be published. Required fields are marked *