SQL Tip: APPLY

[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. 🙂

APPLY

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.

Examples:

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;

Output:

session_id status command sql_handle blocking_session_id
69 running SELECT 0x0200000054425B0A485B22889D18AD295A96562DF1CD26440000000000000000000000000000000000000000 0

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);

Output:

text
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;

Output:

session_id status command sql_handle blocking_session_id SQL_Text
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';

Output:

Name SiteCode ServerRole SQLInstance ProviderServerName
1234CASSiteServ.contoso.com CAZ CCAR 1234CASSITESERV.CONTOSO.COM 1234CASSiteServ.contoso.com
PriSiteR1PS1.contoso.com RD3 Peer PRISITER1SQL01.CONTOSO.COM PriSiteR1SQL01.contoso.com
PriSiteR2PS1.contoso.com RD2 Peer PRISITER2SQL01.CONTOSO.COM PriSiteR2SQL01.contoso.com
PriSiteN1PS1.contoso.com NA1 Peer PRISITEN1SQL02.CONTOSO.COM PriSiteN1PS1.contoso.com
PriSiteEuropePS1.contoso.com EU1 Peer PriSiteEuropeSQ1.CONTOSO.COM PriSiteEuropePS1.contoso.com
PriSiteAsiaPS1.contoso.com AU1 Peer PriSiteAsiaSQL01.CONTOSO.COM PRISITEASIAPS1.contoso.com
IntuneDevicesPS1.contoso.com INT Peer INTUNESQL01.CONTOSO.COM IntuneDevicesPS1.contoso.com

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!

Advertisements

2 thoughts on “SQL Tip: APPLY

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s