April’s T-SQL Tuesday is brought to us by Matt Velic (Blog|Twitter). I met Matt for the first time in summer 2010 when we both set sail on the first ever SQLCruise. Today he’s tasked us to write about the T-SQL APPLY operator and “share how you use this wonderful feature.”
To me, a wonderful thing about APPLY is that you must specify whether you want to perform a CROSS APPLY or OUTER APPLY. So it’s not like the JOIN operator where INNER JOIN is implied if you just specify JOIN all by itself. I know, there are standards to consider, and I appreciate that it’s not hard to remember what JOIN all by itself means. It’s just that it is not very readable to me when I’m looking over a bunch of SQL code to see the word JOIN all by itself. Okay, this is not supposed to be about the JOIN operator, so I’ll move along.
But really, why does somebody just type the word JOIN instead of INNER JOIN? Is it really that hard to type five extra letters? Do they think the query will perform faster because there’s one less word to analyze? Okay, okay, enough already, I’ll get on with it.
So far I’ve mostly used the APPLY operator when utilizing dynamic management functions in SQL Server. While these are table-valued functions, they typically get classified under the umbrella of DMV (dynamic management views) in SQL Server 2005 and newer. My favorite book on DMVs is Performance Tuning with SQL Server Dynamic Management Views by Louis Davidson (Blog|Twitter) and Tim Ford (Blog|Twitter). Last year I wrote a review of that book. If you aren’t familiar with it, then I’d recommend that you consider picking up a copy, regardless of whether you are a developer or DBA.
A last thing I’ll mention about the APPLY operator… one might get the impression that it’s only useful when table-valued functions are involved. But it offers more, and for that I’d recommend checking out Kendra Little’s (Blog|Twitter) post from two weeks ago on Using APPLY for calculations. I’ve been addressing performance issues in a client’s T-SQL source code that’s used for generating reports. It was all written in the pre-2005 days when APPLY was not available. So the APPLY operator will be among the options I consider when improving or writing such calculation-intensive T-SQL code.
I’m looking forward to reading the contributions to this month’s T-SQL Tuesday and finding out how everyone is using APPLY. Thanks to Matt Velic for hosting T-SQL Tuesday #17, and thanks again to Adam Machanic (Blog|Twitter) for creating this monthly blog event!