Have you ever come across the APPLY clause in SQL Server and wondered what it does? In this blog post, we will explore the APPLY clause and its functionality.

The APPLY operator allows you to invoke a table-valued function for each row returned by an outer table expression of a query. The table-valued function acts as the right input and the outer table expression acts as the left input. The right input is evaluated for each row from the left input and the rows produced are combined for the final output.

One practical use case of the APPLY clause is retrieving the latest execution plan from the cache. By using the following query:

SELECT deqs.last_execution_time AS [Time],
       dest.TEXT AS [Query],
       decp.query_plan
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) AS decp
ORDER BY deqs.last_execution_time DESC

This query returns the query text and query execution plan in XML from the memory cache. It utilizes the APPLY clause to invoke the table-valued functions sys.dm_exec_sql_text and sys.dm_exec_query_plan for each row returned by the outer table expression.

Examining the execution plan of the query, you will notice that there are four Table Valued Functions being used. One of these functions, FNGETSQL, retrieves the SQL Text. Interestingly, there is another function that performs the same task with the same number. By modifying the query to use sys.fn_get_sql instead:

SELECT deqs.last_execution_time AS [Time],
       dest.TEXT AS [Query],
       decp.query_plan
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.fn_get_sql(deqs.sql_handle) AS dest
CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) AS decp
ORDER BY deqs.last_execution_time DESC

You will obtain the same result, and if you compare the execution plans, you will find that they are almost identical with the same query cost. This demonstrates that SQL Server provides multiple ways to achieve the same result, while underlying the technology remains the same.

Understanding the APPLY clause in SQL Server can greatly enhance your query capabilities and allow you to efficiently retrieve and manipulate data. It is a powerful tool that should be in every SQL Server developer’s toolkit.

Thank you for reading this blog post. Stay tuned for more SQL Server tips and tricks!