SQL SERVER – 2005 – CROSS APPLY

Yesterday I wrote article about SQL SERVER – 2005 – Last Ran Query – Recently Ran Query. I had used CROSS APPLY in the query. I got email from one reader asking what is CROSS APPLY. In simpler words, cross apply is like 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 example of CROSS APPLY in my article here.

For further example and explanation of the CROSS APPLY I recommend the example from BOL, which is quite clear about what CROSS APPLY can do.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

6 thoughts on “SQL SERVER – 2005 – CROSS APPLY

  1. 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

    Like

  2. 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

    Like

  3. 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

    Like

  4. 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.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s