As it turns out, the functions used in both examples return data for all rows in the outer tables. In the examples above, the CROSS APPLY operator returns the rows in the outer table for which the table value function returns data. Because a product is associated with only one product model in the SalesOrderDetail table, there will never be more than one row per product.
One thing you might notice about the results shown here, compared to the results shown in the preceding example, is that the function returns only one row for each product. The following table shows part of the results returned by the SELECT statement. Notice that I use the CROSS APPLY operator in the FROM clause to join the CTE to the function, as I would use the operator to join a table or view to the function. I then use the CTE in the main SELECT statement, along with the fn_products function. The CTE returns the total sales for each product as they appear in the Sales.SalesOrderDetail table. Notice that I first define a CTE named ProductSales. To demonstrate how you can use the APPLY operator with a CTE, I created the following function, which returns the product model associated with the specified product: Using the CROSS APPLY Operator with a CTE Not let’s look at another example that uses the operator with a common table expression (CTE). That’s all there is to using the CROSS APPLY operator. Notice that each salesperson is listed three times, once for each result returned by the fn_sales function.
The following table shows part of the results returned by the SELECT statement above. For example, because the SalesAmount column in the SELECT list is returned by the function, I qualify the column name as fn.SalesAmount. I assign an alias to the table (sp) and one to the function (fn) and then reference the columns accordingly. In the original version of the AdventureWorks database, the column name in vSalesPerson is SalesPersonID, just like it is in the SalesOrderHeader table.Īlso notice that the columns in the SELECT list reference the source table and function as they would if I were joining two tables. NOTE: The BusinessEntityID column in the vSalesPerson view uses the same IDs that are used in the SalesPersonID column in the SalesOrderHeader table.
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: Let’s look at an example to demonstrate how this works. Using the CROSS APPLY OperatorĪs I mentioned, CROSS APPLY returns only those rows in the outer table for which the table value function returns data. 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. 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. The examples I show you were created on a local instance of SQL Server 2008 and the AdventureWorks2008 sample database.
In this article, I demonstrate how to work with both forms of the APPLY operator. So an employee would be listed even if that employee held no specific position. The OUTER APPLY form, on the other hand, returns all rows from the outer table, even if the function produces no results. 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 CROSS APPLY operator returns rows from the primary (outer) table only if the table-value function produces a result set. The APPLY operator can take one of two forms: CROSS APPLY or OUTER APPLY. 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. 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. Your result set will then include a row for each position that an employee has held. You can create a function that retrieves the employees’ positions and then evoke that function for each row returned 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. For example, you might create a query that returns a list of employees from the Employee table. 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. SQL Server APPLY Basics - Simple Talk Skip to content