APPLY Yourself
ABSTRACT
Microsoft introduced the APPLY operator in SQL Server 2005. This under-used, non-standard operator can help us solve a number of common (and not-so-common) problems with elegant code which performs well. We will look several problem spaces in which the APPLY operator shines, such as joining to table-valued functions, getting the top N child records for each parent record, string splitting, and word matching. With the use of these examples, we will gain a deeper understanding of when to use the APPLY operator.
ADDITIONAL MEDIA
On January 7, 2025, I SQL Data Partners podcast website.
SLIDES
Click here to access the slides for this presentation.
The slides are licensed under Creative Commons Attribution-ShareAlike.
DEMO CODE
Click here to access demo code for this presentation.
The source code is licensed under the terms offered by the GPL.
LINKS & FURTHER INFO
References
I used a number of third-party scripts and examples in this demo, and would like to give credit to the authors once more:
- Create super-sized versions of the SalesOrderHeader and SalesOrderDetail tables. This Jonathan Kehayias script gives us a couple of AdventureWorks tables which are large enough to show real performance tuning results.
- Pre-aggregating data using the APPLY operator. Jason Strate shows the performance benefit from aggregating early using CROSS APPLY. He also has a version using a correlated sub-query.
- Ever since reading Kendra Little's blog post on how to simplify calculations using the APPLY operator, I have been crazy about doing this. It really makes your T-SQL code a lot cleaner when your SELECT statement mentions a calculation rather than performing it half a dozen times.
- Brad Schulz has an extreme example of simplifying calculations using APPLY. I've never run into anything quite this crazy in real life, but it's an incredibly instructive demo.
- I also used Brad Schulz's demo to split strings. I prefer using tally tables or the CLR for string splitting, but this performs very well too and it's worth mentioning.
- My last demo walks through Brad Schulz's word combinations blog post. This is a great test to see how well you understand the APPLY operator: if you can walk through the code and explain it piece by piece, you're good here.
In addition to those references, here are a couple more which helped shape my presentation:
- Matt Velic hosted T-SQL Tuesday #17 and chose the APPLY operator as its topic. There are some fantastic blog posts in that list and this is a great start on the topic.
- Bradley Ball shows us how to use APPLY to execute system and user-defined functions.
I also have a few blog posts which make use of the APPLY operator. These are mostly on the administrative side, whereas the presentation focused more on the development side.