Starting with SQL Server 2005, you can use the APPLY operator in a Transact-SQL query to join a table to a table-valued function so the function is evoked for each row returned from the table. For example, you might create a query that returns a list of employees from the Employee table. For each employee, you also want to return a list of the individual job positions that person has held in the company.

You can create a function that retrieves the employees’ positions and then evoke that function for each row returned from the Employee table. Your result set will then include a row for each position that an employee has held. For instance, if John has worked as a salesman and a regional supervisor, the result set will include two rows for John, one for each position. Any data returned from the Employee table will be repeated for each row, but the data returned by the function will be specific to each row in the function’s results.

The APPLY operator can take one of two forms: CROSS APPLY or OUTER APPLY. The CROSS APPLY operator returns rows from the primary (outer) table only if the table-value function produces a result set. That means, in the example above, an employee would be included in the returned data only if that employee has held a specific position within the company. The OUTER APPLY form, on the other hand, returns all rows from the outer table, even if the function produces no results. So an employee would be listed even if that employee held no specific position.

In this article, I demonstrate how to work with both forms of the APPLY operator. The examples I show you were created on a local instance of SQL Server 2008 and the AdventureWorks2008 sample database. If you want to run these examples against the AdventureWorks database on an instance of either SQL Server 2005 or 2008, you must change references to the BusinessEntityID column to SalesPersonID column, where appropriate. In addition, you’ll find that your results might vary slightly from those shown here because the values between the databases are slightly different, particularly primary key values. Using the CROSS APPLY Operator

As I mentioned, CROSS APPLY returns only those rows in the outer table for which the table value function returns data. Let’s look at an example to demonstrate how this works. Fist, we’ll create a function that returns the top three sales generated by a salesperson, as those sales appear in the Sales.SalesOrderHeader table in the AdventureWorks2008 database:

USE AdventureWorks2008
GO
IF OBJECT_ID (N'fn_sales', N'IF') IS NOT NULL
DROP FUNCTION dbo.fn_sales
GO
CREATE FUNCTION fn_sales (@SalesPersonID int)
RETURNS TABLE
AS
RETURN
(

SELECT TOP 3
SalesPersonID,
ROUND(TotalDue, 2) AS SalesAmount
FROM
Sales.SalesOrderHeader
WHERE
SalesPersonID = @SalesPersonID
ORDER BY
TotalDue DESC
)
GO

As you can see, the fn_sales function takes one parameter, @SalesPersonID, which is configured with the int data type. The function returns the three highest sales for the specified salesperson. Note that this is a table-valued function, which means that it returns the entire result set generated by the SELECT statement. For the fn_sales function, the result set includes the SalesPersonID and SalesAmount columns.

After you create your function, you can test it by running a SELECT statement that retrieves data from the function. For example, the following SELECT statement returns the SalesAmount column for salesperson ID 285:

Full article...