[edit: April 2019] This was originally posted to my internal (to Microsoft) blog on May 14, 2013 as part of a series I called ‘SQL Tips’ for coworkers and an email distribution list people could sign up to. I have left this as originally written but have updated formatting for the my WordPress theme (and renamed servers).
As I’ve been thinking about what my next SQL tip would be I realized that it’s time to start talking about querying XML via SQL. However, before I can talk about some of the XQuery concepts there are some things that you should know about first. Hence this discussion on APPLY.
There are two types of APPLY: the CROSS APPLY and the OUTER APPLY. The APPLY is always part of the FROM statement and can somewhat be likened to a JOIN. For the sake of simplicity we’ll use examples that show how to use APPLY with only two objects (table/view/function/etc). I do reserve the right to change my mind if I need to though. 🙂
First let’s take a look at how the APPLY is constructed; here’s an example:
SELECT * -- Note: Don't do this! The asterisk is for disply purposes only 🙂 FROM dbo.SomeTableOrView obj CROSS APPLY dbo.SomeTableValuedFunction(obj.ColumnOne) tvf;
As you can see APPLY is part of the FROM statement, comes after at least one object and does not have an “ON” condition. What the APPLY does is: for every record returned from the input table (the “left” table or in our example “SomeTableOrView”) the table valued function (the “right” object or in our example “SomeTableValuedFunction”) is evaluated for the record. It is possible for the table valued function that is called for each of the input values to return zero, one, or more records for each input value. What is eventually returned in the result set depends on whether you use a CROSS or OUTER APPLY.
CROSS vs OUTER
A CROSS APPLY acts somewhat similarly to an INNER JOIN in that the result set that is returned will only contain the records where the table valued function returned at least one record for an evaluated record from the input or left table. Thus, if a record is returned but the APPLY doesn’t create any records then the original record is omitted from the result set.
An OUTER APPLY acts similarly to a LEFT OUTER JOIN in that every record from the input or left table will be returned in the result set and any records returned by the table valued function will also be returned. If an input record doesn’t have a corresponding record then NULL values will be shown for the resulting properties.
In both cases if more than one record is returned by the function then all these records will be added to the result set and the values from the input record will be copied/duplicated.
Let’s start with a pretty common query used by DBAs. If a DBA wants to see the “requests” being executed against the server they can look at “dm_exec_requests”. This view contains something called the “sql_handle”. If we run the following query we’ll see results similar to what comes after the query.
SELECT req.session_id ,req.status ,req.command ,req.sql_handle ,req.blocking_session_id FROM sys.dm_exec_requests req;
Hmmm…yeah…that “sql_handle” doesn’t really help me much because I don’t know how to make any sense out of that. Therefore, we can run another query which uses a function to determine the “text” of the sql_handle:
SELECT text FROM sys.dm_exec_sql_text(0x0200000018025818ECC73DE301B7603AD180E92164DF2ABF0000000000000000000000000000000000000000);
|SELECT req.session_id ,req.status ,req.command ,req.sql_handle ,req.blocking_session_id FROM sys.dm_exec_requests req;|
Pretty cool right? Well, because we know about APPLY we can use one query to get all this information:
SELECT req.session_id ,req.status ,req.command ,req.sql_handle ,req.blocking_session_id ,txt.text AS [SQL_Text] FROM sys.dm_exec_requests req CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) txt;
|69||running||SELECT||0x020000009877CD246276EBF9DB0EF4BFF6FAE233931D7E3D0000000000000000000000000000000000000000||0||SELECT req.session_id….(truncated for space!)|
Some of you may ask “couldn’t I just use the function in the SELECT statement by doing something like the following?
SELECT req.session_id ,req.status ,req.command ,req.sql_handle ,(SELECT text FROM sys.dm_exec_sql_text(req.sql_handle)) AS [SQL_Text] ,req.blocking_session_id FROM sys.dm_exec_requests req WHERE sql_handle IS NOT NULL;
Yes, yes you could. But that wouldn’t make for a way for me to talk about APPLY now would it? 🙂 Also, there can be performance increases using APPLY (but not always). As always, you have to test with your specific query and system to see what will be best in your specific situation.
Next let’s look at a Configuration Manager specific example. One of my favorite tables is “ServerData”; it contains a record for each CAS (CCAR), Primary (Peer), and Secondary (Proxy) server in the environment along with some additional data. There also happens to be a table valued function named “fnGetSc_SiteDefinition_Property” which will give information on a site. If I want to know the “ProviderServerName” of a particular site I can pass the “SiteNumber” and the aforementioned property name and get this value. Rather than run this function one site at a time I’m going to create an input set which is the list of all primary sites as well as the CAS site and run the function for each site in the input set. Just in case there isn’t something listed for the “ProviderServerName” I’m going to use OUTER APPLY. Here’s what that query looks like:
SELECT srv.Name ,srv.SiteCode ,srv.ServerRole ,srv.SQLInstance ,prv.Value1 AS [ProviderServerName] FROM dbo.ServerData srv OUTER APPLY dbo.fnGetSc_SiteDefinition_Property(N'ProviderServerName', srv.ID) prv WHERE srv.ServerRole != N'Proxy';
So there you have it, the APPLY in all its glory. Hopefully you now understand what APPLY is and how it can be used.
As always, I welcome your feedback or questions!