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:
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:
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:
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:
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:
- Session ID
- CPU Time
- Wait Type
- Database ID
- Reads (Physical)
- Writes (Physical)
- Logical reads
- SQL Handle
- Plan Handle
- Query Status
- Command
- 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:
- Database ID
- Object ID
- Is Encrypted
- 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:
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:
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:
- Database ID
- Object ID
- Is Encrypted
- 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:
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:
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:
- Database ID
- Object ID
- Index ID
- Partition Number
- 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:
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.
