APPLY - used exclusively when joining a function

Question

The APPLY statement is not an operator that has experience with use, and I worry that I am missing out on certain features.

This article by Alexander Kuznetsov gives a good example.

In Alexander’s example, he uses APPLY to attach to a function return — are there other situations where APPLY , CROSS or OUTER should be the first that I use?

Answer 1

APPLY is a line-by-line statement that allows APPLY use either a function or a subquery on each row of previous tables. Therefore, it is very useful in situations where

• you need the last record / maximum / minimum record from B from each record in A
• you need to run a table function for each entry in A

Yes, this can replace REGISTER, but it would do terribly when a normal REGISTER would work, because you force SQL Server to use line by line of statements, and not install based on JOIN.

The difference between CROSS APPLY and OUTER APPLY is the same as the difference between INNER JOIN and OUTER JOIN. Basically, CROSS APPLY deletes the original record if the function / subquery to which it is applied returns 0 records. OUTER APPLY saves the source record.

Read more: Using Apply (msdn)

Answer 2

APPLY can join an arbitrary set of results. It is especially useful for me to return the TOP 1 line in some order:

 select * from T cross apply ( select top 1 * from T2 order by DateTime desc ) x 

APPLY can do anything it can join and more. But he cannot use links.

Basically, you want to use a connection if the connection is enough. Use if you need more, because it's a little more cumbersome to use.

Answer 3

Basically, use a join to combine the two result sets, but CROSS APPLY behaves like a loop that takes an external result from a table and then passes each row to a table or table function as a parameter to create a result set. There are two uses for CROSS APPLY and OUTER APPLY. CROSS APPLY t returns only rows that correspond to a table table or table function, and the result of OUTER APPLY causes all records in the external table and disk table mapping to try to avoid application because the slow query then joins