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 October 14, 2015, Carlos L. Chacon interviewed me for his SQL Data Partners podcast. You can get the recording and show notes on the 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.