SQL SERVER – Quick Note on CROSS APPLY

SQL SERVER - Quick Note on CROSS APPLY xsym 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)

, ,
Previous Post
SQL SERVER – 2005 – Last Ran Query – Recently Ran Query
Next Post
SQL SERVER – Most Frequently Asked Generic Interview Questions

Related Posts

6 Comments. Leave new

  • Shashi Kant Chauhan
    January 11, 2008 9:12 pm

    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

    Reply
  • really fine and helpful………

    thank you!!!!!!!!!1111
    …….Ak

    Reply
  • Example of Cross apply and outer apply

    Reply
  • 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

    Reply
  • 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

    Reply
  • 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.

    Reply

Leave a Reply

Menu