The APPLY operator allows you to invoke a table-valued function for each row returned by an outer expression of a query.
USE AdventureWorksSELECT 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.