Yesterday I wrote an article about SQL SERVER – 2005 – Last Ran Query – Recently Ran Query. I had used CROSS APPLY in the query. I got an email from one reader asking what is CROSS APPLY. In simpler words, cross apply is like an inner join to table valued function which can take parameters. This particular operation is not possible to do using regular JOIN syntax You can see an example of CROSS APPLY in my article here.
How many times we have wondered what were the last few queries ran on SQL Server? Following quick script demonstrates last ran query along with the time it was executed on SQL Server 2005.
SELECT deqs.last_execution_time AS [Time], dest.TEXT AS [Query] FROM sys.dm_exec_query_stats AS deqs CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest ORDER BY deqs.last_execution_time DESC
For further example and explanation of the CROSSAPPLY I recommend the example from BOL, which is quite clear about what CROSS APPLY can do. CROSSAPPLY works better on things that have no simple JOIN condition. It returns only rows from the outer table that produce a result set from the table-valued function. It other words, a result of CROSSAPPLY doesn’t contain any row of left side table expression for which no result is obtained from the right side table expression. CROSSAPPLY work as a row by row INNER JOIN.
Reference : Pinal Dave (https://blog.sqlauthority.com)