This article is dedicated to the operator APPLY, and more specifically its type CROSS APPLY. This statement was first introduced in SQL Server 2005, but unfortunately many have not learned how to use it, maybe it's because of the fact that the BOL (SQL ServerBooksOnline) this operator is not well and has a very "dry" examples of its use. In this article, I'll show some interesting demonstrations, where the operator can come in handy.

The main feature of the operator is that APPLY allows you to invoke a table function for each row returned by the outer table expression query. It is this example is in BOL.

CROSS APPLY operator returns only the rows from the outer table that creates a result set from a table-valued function. OUTER APPLY operator and returns the lines that form the result set, and the lines that do not, with NULL values ??in the columns created a table-valued function.

To demonstrate some of fitch operator APPLY, create a test database and a couple of tables.

The result of the function call is as follows:

Now, using the APPLY operator with a list of cities for each country from the table Countries:

So, very convenient, but a similar example is described in BOL. Next you`ll see how can still use the operator CROSS APPLY. For example, how to withdraw from 3 cities in each country, sorted alphabetically? Using APPLY operator is relatively easy:

Now try to further complicate our request. We derive the first letter of each of the 3 cities in each country and the total number of characters of any city in the current country:

As you can see the operator CROSS APPLY very useful