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)
6 Comments. Leave new
I will very thankful to you if you add the details and example of OUTER APPLY and what is the difference between CROSS APPLY and OUTER APPLY………..
Thanks & Regards
Shashi Kant Chauhan
really fine and helpful………
thank you!!!!!!!!!1111
…….Ak
Example of Cross apply and outer apply
Do this APPLY operator have any advantage over JOINS..??
Also Can you please explain little more details on CROSS APPLY and OUTER APPLY with Examples.
Thanks
Suresh
hi ,
I want to update rows in a single column 1 by 1,
like sales of a company get changes then all the other variable of the column will get chgange. So i need to calculate all the rows/cells according to formula.
As we do in excel-
Ex column G
G5(Sales)=G6*G7(sales is user input from front end , it will be increased or decreased by user)
G11=G5*G10
G12=G11-G10
Something like this if g5 changes all the below cells values will be updated,
Is croos apply useful in the above scenario
Hi Suresh.
In Simple words. an APPLY is used to join a table-valued-function with a table. APPLY are of two types: CROSS APPLY and OUTER APPLY
A CROSS APPLY is used when you want to return only those records from your table where a value is returned by your table valued function.
Whereas, an OUTER APPLY is used to return all records from your table regardless of the values returned by your table valued function.
So far, what I have understood is that it is a good to have feature and gives nice readability to your queries.
I have also read on a few internet articles that APPLY is a costly affair when compared with JOINS. I still haven’t any proofs or any trusted matter which can prove that and also the advantages of using APPLY.
The only one advantage that I know about is that it helps the query execution plan to determine the correct index joins.
Any more inputs from anybody are welcome.
Thanks,
Yashpal Shah.