SQL: APPLY OPERATOR

The APPLY operator allows you to invoke a table-valued function for each row returned by an outer expression of a query.

USE AdventureWorks
SELECT qs.sql_handle,qt.text
FROM sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text (qs.sql_handle) as qt
order by qs.sql_handle ASC

The sys.dm_exec_sql_text( ) is a function. It returns a table.

The APPLY operator does the following:

For each record returned from sys.dm_exec_query_stats, the sys.dm_exec_sql_text( ) runs. The result set is the combined columns from both sys.dm_exec_query_stats and sys.dm_exec_sql_text.