Systems Engineering and RDBMS

APPLY Operator in SQL Server 2005

Posted by decipherinfosys on October 8, 2007

In SQL Server 2005, Microsoft introduced the APPLY operator. We have used that in some of our previous blog posts to demonstrate some code – you can read about those examples here and here. In this post, we will cover what this operator really does. The APPLY operator allows one to invoke a table valued function for each row returned by an outer expression query. Basically, SQL Server allows us to join table and table valued function in such a way that for each record returned from outer query, we can call table valued function to retrieve the data. This was not possible in versions prior to 2005 and was done using a derived table/view.APPLY operator can be used in two ways:

• CROSS APPLY: It is like an inner join. Returns only those rows from the outer query, for which table valued function returns some data.
• OUTER APPLY: Its behavior is similar to outer join. It returns all the rows from the outer query irrespective of data returned by table function. If table function returns data then column values pertaining to table function will be not null other wise it will contain null values.

Let us use the DMVs to illustrate this functionality. We would like to find a query text for our current batch. To find this out we need to use sys.dm_exec_requests dynamic management view in conjunction with sys.dm_exec_sql_text table function. First, we will see what is being returned by sys.dm_exec_requests. It returns lots of valuable information like command, session_id, database_id, transaction_id, sql_handle and many more but at this point we are interested only in session_id, commnad and sql_handle. Connect to appropriate database using SQL Server management studio.

SELECT r.session_id, r.command, r.sql_handle
FROM sys.dm_exec_requests r
GO

And abbreviated output is as shown below.

session_id command sql_handle
———- —————- ———–
18 TASK MANAGER NULL
19 TASK MANAGER NULL
23 TASK MANAGER NULL
52 SELECT 0x020000006435D33223D66E660138A9905F1ECD1136EEE3B2

In order to see the text for interested query, we need to invoke sys.dm_exec_sql_text table valued function which requires sql_handle as an input parameter. So here is the query in isolation to get the query text followed by an output.

SELECT text
FROM sys.dm_exec_sql_text(0x020000005097B03AC054C5C0E9C91CEE079B942A54BBDA7D);
GO

text
————————————
SELECT * FROM sys.dm_exec_requests;

We can combine both the queries and using CROSS APPLY operator, we can get the same results. Here is the query.

SELECT r.session_id, r.command, t.text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
GO

Output is as shown below.

session_id command text
———- —————- —————————————
52 SELECT SELECT r.session_id, r.command, t.text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t

Since above query was the last one to execute, text column shows the text of the last query. CROSS APPLY returned only one row from outer query for which table valued function returned the data. Now let us change CROSS APPLY to OUTER APPLY and see the difference.

SELECT r.session_id, r.command, t.text
FROM sys.dm_exec_requests r
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) t
GO

Here is the abbreviated output.

session_id command text
———- —————- —————————————
18 TASK MANAGER NULL
19 TASK MANAGER NULL
23 TASK MANAGER NULL
52 SELECT SELECT r.session_id, r.command, t.text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t

This time, the result set also includes the rows from the outer query for which table valued function returned the null value.

5 Responses to “APPLY Operator in SQL Server 2005”

  1. […] and ending position is more than -1, then it will be for a specific query within the batch. Using cross apply operator, we can even return the text plan for each row returned by dynamic management view. Following is […]

  2. […] the cross apply operator that we had discussed in one of our previous blog posts, one can write up a simple script to show […]

  3. […] in SQL Server, one can also use the CROSS APPLY function in SQL Server […]

  4. […] we converted the image data type –> binary and then to XML and at the top of that we did a CROSS APPLY using the nodes() method and extracted the name of the report, the command type and the command […]

  5. […] now, we can extract this data using the nodes() method and applying CROSS APPLY at it to extract out each […]

Sorry, the comment form is closed at this time.

 
%d bloggers like this: