Written by 17:23 Database development, Statements

Advanced SQL: CROSS APPLY and OUTER APPLY

In this article, we’ll look into the “APPLY” operator and its variations – CROSS APPLY and OUTER APPLY along with examples of how they can be used.

In particular, we will learn:

  • the difference between CROSS APPLY and the JOIN clause
  • how to join the output of SQL queries with table-evaluated functions
  • how to identify performance issues by querying dynamic management views and dynamic management functions.

What the APPLY Clause is

Microsoft introduced the APPLY operator in SQL Server 2005. The APPLY operator is similar to the T-SQL JOIN clause as it also allows you to join two tables – for example, you can join an outer table with an inner table. The APPLY operator is a good option when, on one side, we have a table-evaluated expression that we want to evaluate for each row from the table we have on another side. So, the right-side table is processed for each row of the left-side table. The left-side table is evaluated first, and then the right-side table is evaluated against each row of the left-side table to generate the final result set. The final result set includes all columns from both tables.

The APPLY operator has two variations:

  • CROSS APPLY
  • OUTER APPLY

CROSS APPLY

CROSS APPLY is similar to INNER JOIN, but can also be used to join table-evaluated functions with SQL Tables. CROSS APPLY’s final output consists of records matching between the output of a table-evaluated function and an SQL Table.

OUTER APPLY

OUTER APPLY resembles LEFT JOIN, but has an ability to join table-evaluated functions with SQL Tables. OUTER APPLY’s final output contains all records from the left-side table or table-evaluated function, even if they don’t match with the records in the right-side table or table-valued function.

Now, let me explain both variations with examples.

Usage examples

Preparing the Demo Setup

To prepare a demo setup, you will need to create tables named “Employees” and “Department” in a database we’ll call “DemoDatabase”. To do that, run the following code:

USE DEMODATABASE 
GO 

CREATE TABLE [DBO].[EMPLOYEES] 
  ( 
     [EMPLOYEENAME] [VARCHAR](MAX) NULL, 
     [BIRTHDATE]    [DATETIME] NULL, 
     [JOBTITLE]     [VARCHAR](150) NULL, 
     [EMAILID]      [VARCHAR](100) NULL, 
     [PHONENUMBER]  [VARCHAR](20) NULL, 
     [HIREDATE]     [DATETIME] NULL, 
     [DEPARTMENTID] [INT] NULL 
  ) 

GO 

CREATE TABLE [DBO].[DEPARTMENT] 
  ( 
     [DEPARTMENTID]   INT IDENTITY (1, 1), 
     [DEPARTMENTNAME] [VARCHAR](MAX) NULL 
  ) 
GO

Next, insert some dummy data into both tables. The following script will insert data into the “Employees” table:

[expand title =”FULL QUERY“]

INSERT [DBO].[EMPLOYEES] 
       ([EMPLOYEENAME], 
        [BIRTHDATE], 
        [JOBTITLE], 
        [EMAILID], 
        [PHONENUMBER], 
        [HIREDATE], 
        [DEPARTMENTID]) 
VALUES (N'KEN J SÁNCHEZ', 
        CAST(N'1969-01-29T00:00:00.000' AS DATETIME), 
        N'CHIEF EXECUTIVE OFFICER', 
        N'[email protected]', 
        N'697-555-0142', 
        CAST(N'2009-01-14T00:00:00.000' AS DATETIME), 
        1), 
       (N'TERRI LEE DUFFY', 
        CAST(N'1971-08-01T00:00:00.000' AS DATETIME), 
        N'VICE PRESIDENT OF ENGINEERING', 
        N'[email protected]', 
        N'819-555-0175', 
        CAST(N'2008-01-31T00:00:00.000' AS DATETIME), 
        NULL), 
       (N'ROBERTO  TAMBURELLO', 
        CAST(N'1974-11-12T00:00:00.000' AS DATETIME), 
        N'ENGINEERING MANAGER', 
        N'[email protected]', 
        N'212-555-0187', 
        CAST(N'2007-11-11T00:00:00.000' AS DATETIME), 
        NULL), 
       (N'ROB  WALTERS', 
        CAST(N'1974-12-23T00:00:00.000' AS DATETIME), 
        N'SENIOR TOOL DESIGNER', 
        N'[email protected]', 
        N'612-555-0100', 
        CAST(N'2007-12-05T00:00:00.000' AS DATETIME), 
        NULL), 
       (N'GAIL A ERICKSON', 
        CAST(N'1952-09-27T00:00:00.000' AS DATETIME), 
        N'DESIGN ENGINEER', 
        N'[email protected]', 
        N'849-555-0139', 
        CAST(N'2008-01-06T00:00:00.000' AS DATETIME), 
        NULL), 
       (N'JOSSEF H GOLDBERG', 
        CAST(N'1959-03-11T00:00:00.000' AS DATETIME), 
        N'DESIGN ENGINEER', 
        N'[email protected]', 
        N'122-555-0189', 
        CAST(N'2008-01-24T00:00:00.000' AS DATETIME), 
        NULL), 
       (N'DYLAN A MILLER', 
        CAST(N'1987-02-24T00:00:00.000' AS DATETIME), 
        N'RESEARCH AND DEVELOPMENT MANAGER', 
        N'[email protected]', 
        N'181-555-0156', 
        CAST(N'2009-02-08T00:00:00.000' AS DATETIME), 
        3), 
       (N'DIANE L MARGHEIM', 
        CAST(N'1986-06-05T00:00:00.000' AS DATETIME), 
        N'RESEARCH AND DEVELOPMENT ENGINEER', 
        N'[email protected]', 
        N'815-555-0138', 
        CAST(N'2008-12-29T00:00:00.000' AS DATETIME), 
        3), 
       (N'GIGI N MATTHEW', 
        CAST(N'1979-01-21T00:00:00.000' AS DATETIME), 
        N'RESEARCH AND DEVELOPMENT ENGINEER', 
        N'[email protected]', 
        N'185-555-0186', 
        CAST(N'2009-01-16T00:00:00.000' AS DATETIME), 
        3), 
       (N'MICHAEL  RAHEEM', 
        CAST(N'1984-11-30T00:00:00.000' AS DATETIME), 
        N'RESEARCH AND DEVELOPMENT MANAGER', 
        N'[email protected]', 
        N'330-555-2568', 
        CAST(N'2009-05-03T00:00:00.000' AS DATETIME), 
        3)

[/expand]

To add data to our “Department” table, run the following script:

INSERT [DBO].[DEPARTMENT] 
       ([DEPARTMENTID], 
        [DEPARTMENTNAME]) 
VALUES (1, 
        N'IT'), 
       (2, 
        N'TECHNICAL'), 
       (3, 
        N'RESEARCH AND DEVELOPMENT')

Now, to verify the data, execute the code you can see below:

SELECT [EMPLOYEENAME], 
       [BIRTHDATE], 
       [JOBTITLE], 
       [EMAILID], 
       [PHONENUMBER], 
       [HIREDATE], 
       [DEPARTMENTID] 
FROM   [EMPLOYEES] 

GO

SELECT [DEPARTMENTID], 
       [DEPARTMENTNAME] 
FROM   [DEPARTMENT] 
GO

Here’s the desired output:

SQL APPLY demo tables

Creating and testing a table-evaluated function

As I already mentioned, “CROSS APPLY” and “OUTER APPLY” are used to join SQL Tables with table-evaluated functions. To demonstrate that, let’s create a table-evaluated function named “getEmployeeData.” This function will use a value from the DepartmentID column as an input parameter and return all employees from the correspondent department.

To create the function, run the following script:

CREATE FUNCTION Getemployeesbydepartment (@DEPARTMENTID INT) 
RETURNS @EMPLOYEES TABLE ( 
  EMPLOYEENAME   VARCHAR (MAX), 
  BIRTHDATE      DATETIME, 
  JOBTITLE       VARCHAR(150), 
  EMAILID        VARCHAR(100), 
  PHONENUMBER    VARCHAR(20), 
  HIREDATE       DATETIME, 
  DEPARTMENTID VARCHAR(500)) 
AS 
  BEGIN 
      INSERT INTO @EMPLOYEES 
      SELECT A.EMPLOYEENAME, 
             A.BIRTHDATE, 
             A.JOBTITLE, 
             A.EMAILID, 
             A.PHONENUMBER, 
             A.HIREDATE, 
             A.DEPARTMENTID 
      FROM   [EMPLOYEES] A 
      WHERE  A.DEPARTMENTID = @DEPARTMENTID 

      RETURN 
  END

Now, to test the function, we will pass “1” as “departmentID” to the “Getemployeesbydepartment” function. To do this, execute the script provided below:

USE DEMODATABASE
GO
SELECT EMPLOYEENAME,
       BIRTHDATE,
       JOBTITLE,
       EMAILID,
       PHONENUMBER,
       HIREDATE,
       DEPARTMENTID
FROM   GETEMPLOYEESBYDEPARTMENT (1)

The output should be as follows:

SQL APPLY function test

Joining a table with a table-evaluated function using CROSS APPLY

Now, let’s try to join the Employees table with the “Getemployeesbydepartment” table-evaluated function using CROSS APPLY. As I mentioned, the CROSS APPLY operator is similar to the Join clause. It will populate all records from the “Employee” table for which there are matching rows in the output of “Getemployeesbydepartment”.

Run the following script:

SELECT A.[EMPLOYEENAME], 
       A.[BIRTHDATE], 
       A.[JOBTITLE], 
       A.[EMAILID], 
       A.[PHONENUMBER], 
       A.[HIREDATE], 
       B.[DEPARTMENTNAME] 
FROM   DEPARTMENT B 
       CROSS APPLY GETEMPLOYEESBYDEPARTMENT(B.DEPARTMENTID) A

The output should be as follows:

SQL CROSS APPLY join table and function

Joining a Table with a Table-evaluated function using OUTER APPLY

Now, Let us try to join the Employees table with the “Getemployeesbydepartment” table-evaluated function using OUTER APPLY. As I mentioned before, the OUTER APPLY operator resembles the “OUTER JOIN” clause. It populates all records from the “Employee” table and the output of the “Getemployeesbydepartment” function.

Run the following script:

SELECT A.[EMPLOYEENAME], 
       A.[BIRTHDATE], 
       A.[JOBTITLE], 
       A.[EMAILID], 
       A.[PHONENUMBER], 
       A.[HIREDATE], 
       B.[DEPARTMENTNAME] 
FROM   DEPARTMENT B 
       OUTER APPLY GETEMPLOYEESBYDEPARTMENT(B.DEPARTMENTID) A

Here’s the output you should see as a result:

SQL OUTER APPLY join table and function

Identifying performance issues by using dynamic management functions and views

Let me show you a different example. Here, we’ll see how to get a query plan and the corresponding query text by using dynamic management functions and dynamic management views.

For demonstration purposes, I have created a table named “SmokeTestResults” in the “DemoDatabase”. It contains results of an application smoke test. Let’s imagine that, by mistake, a developer executes a SQL Query to populate the data from “SmokeTestResults” without adding a filter, which significantly reduces the database performance.

As a DBA, we need to identify the resource-heavy query. To do this, we will use the “sys.dm_exec_requests” view and the “sys.dm_exec_sql_text” function.

Sys.dm_exec_requests” is a dynamic management view which provides the following important details we can use to identify the resource-consuming query:

  1. Session ID
  2. CPU Time
  3. Wait Type
  4. Database ID
  5. Reads (Physical)
  6. Writes (Physical)
  7. Logical reads
  8. SQL Handle
  9. Plan Handle
  10. Query Status
  11. Command
  12. Transaction ID

sys.dm_exec_sql_text” is a dynamic management function which accepts an SQL handle as an input parameter and provides the following details:

  1. Database ID
  2. Object ID
  3. Is Encrypted
  4. SQL Query Text

Now, let’s run the following query to generate some stress on the ASAP database. Execute the following query:

USE ASAP 
GO 

SELECT TSID, 
       USERID, 
       EXECUTIONID, 
       EX_RESULTFILE, 
       EX_TESTDATAFILE, 
       EX_ZIPFILE, 
       EX_STARTTIME, 
       EX_ENDTIME, 
       EX_REMARKS 
FROM  [ASAP].[DBO].[SMOKETESTRESULTS]

SQL Server allocates a session ID “66” and starts the query execution. See the following image:

SQL APPLY performance heavy query

Now, to troubleshoot the issue, we require the Database ID, Logical Reads, SQL Query, Command, Session ID, Wait type and SQL Handle. As I mentioned, we can get Database ID, Logical Reads, Command, Session ID, wait Type and SQL handle from “sys.dm_exec_requests.” To get the SQL Query, we must use “sys.dm_exec_sql_text.” It’s a dynamic management function, so would need to join “sys.dm_exec_requests” with “sys.dm_exec_sql_text” by using CROSS APPLY.

In the New query editor window, run the following query:

SELECT B.TEXT, 
       A.WAIT_TYPE, 
       A.LAST_WAIT_TYPE, 
       A.COMMAND, 
       A.SESSION_ID, 
       CPU_TIME, 
       A.BLOCKING_SESSION_ID, 
       A.LOGICAL_READS 
FROM   SYS.DM_EXEC_REQUESTS A 
       CROSS APPLY SYS.DM_EXEC_SQL_TEXT(A.SQL_HANDLE) B

It should produce the following output:

SQL APPLY performance issue identification

As you can see in the above screenshot, the query returned all information required to identify the performance issue.

Now, in addition to the query text, we want to get the execution plan which was used to execute the query in question. To do this, we’ll use the “sys.dm_exec_query_plan” function.

sys.dm_exec_query_plan” is a dynamic management function which accepts a plan handle as an input parameter and provides the following details:

  1. Database ID
  2. Object ID
  3. Is Encrypted
  4. SQL Query Plan in XML format

To populate the query execution plan, we must use CROSS APPLY to join “sys.dm_exec_requests” and “sys.dm_exec_query_plan.

Open the New Query editor window and execute the following query:

SELECT B.TEXT, 
       A.WAIT_TYPE, 
       A.LAST_WAIT_TYPE, 
       A.COMMAND, 
       A.SESSION_ID, 
       CPU_TIME, 
       A.BLOCKING_SESSION_ID, 
       A.LOGICAL_READS, 
       C.QUERY_PLAN 
FROM   SYS.DM_EXEC_REQUESTS A 
       CROSS APPLY SYS.DM_EXEC_SQL_TEXT(A.SQL_HANDLE) B 
       CROSS APPLY SYS.DM_EXEC_QUERY_PLAN (A.PLAN_HANDLE) C

The output should be as follows:

SQL APPLY get query plan

Now, as you can see, the query plan is generated in XML format by default. To open it as a graphical representation, click on the XML output in the query_plan column as shown in the above image. Once you click on the XML output, the execution plan will be opened in a new window as shown in the following image:

SQL APPLY query plan format

Getting a list of tables with highly fragmented indices by using dynamic management views and functions

Let’s see one more example. I want to get a list of tables with indices that have 50% or more fragmentation in a given database. To retrieve these tables, we will need to use the “sys.dm_db_index_physical_stats” view and the “sys.tables” function.

Sys.tables” is a dynamic management view which populates a list of tables on the specific database.

sys.dm_db_index_physical_stats” is a dynamic management function which accepts the following input parameters:

  1. Database ID
  2. Object ID
  3. Index ID
  4. Partition Number
  5. Mode

It returns detailed information on the physical status of the specified index.

Now, to populate the list of fragmented indices, we must join “sys.dm_db_index_physical_stats” and “sys.tables” using CROSS APPLY. Run the following query:

SELECT TABLES.NAME, 
       INDEXSTATISTICS.ALLOC_UNIT_TYPE_DESC, 
       CONVERT(NUMERIC(10, 2), INDEXSTATISTICS.AVG_FRAGMENTATION_IN_PERCENT) AS 
       PERCENTAGEFRAGMENTATION, 
       INDEXSTATISTICS.PAGE_COUNT 
FROM   SYS.TABLES AS TABLES 
       CROSS APPLY SYS.DM_DB_INDEX_PHYSICAL_STATS (DB_ID(), TABLES.OBJECT_ID, 
                   NULL, 
                   NULL, NULL) AS 
                                               INDEXSTATISTICS 
WHERE  INDEXSTATISTICS.DATABASE_ID = DB_ID() 
       AND AVG_FRAGMENTATION_IN_PERCENT >= 50 
ORDER  BY INDEXSTATISTICS.AVG_FRAGMENTATION_IN_PERCENT DESC

The query should produce the following output:

SQL APPLY get fragmented tables

Summary

In this article, we covered the APPLY operator, its variations – CROSS APPLY and OUTER APPLY and how thy work. We have also seen how you can use them to identify SQL performance issues using Dynamic management views and dynamic management functions.

Tags: , , , Last modified: August 08, 2022
Close