Cross Apply Table Functions

SelecTViews
With Stephen Wynkoop
Dell enters the Cloud Provider Space, Nokia breached with SQL Injection Attack, interview with Monotouch developer Michael Bluestein about Development with Monotouch as a technology to use your C# skills on iPhone, iPad and Android platforms.
Watch the Show

$$SWYNK$$

Cross Apply Table Functions
I have really enjoyed the use of TSQL functions in SQL Server. They have made my queries much easier to create and maintain. I especially enjoy the fact that I can create what used to be a Correlated Sub Select in a scalar function and call it within a query.

For example, say I wanted to return every purchase order for a customer, and for each customer I wanted to retrieve the median price of all the items for each individual purchase order. Before the introduction of functions this was a rather difficult problem to solve with TSQL.

Now I write a scalar function calculating the Median price for all items and filter that set by a purchase order id. I include that function in my outer query as demonstrated in this simple code.

CREATE FUNCTION sfnGetMedianPurchaseOrderPrice (@PurchaseOrderID INT)
RETURNS MONEY
AS
BEGIN
DECLARE @Result MONEY
DO SOME MAGIC…Set @Result to the Median Value
RETURN @Result
END

Now I can use this new function in my query.

SELECT PurchaseOrder.PurchaseOrderID
,PurchaseOrder.PurchaseOrder…
,MedianPrice = sfnGetmedianPurchaseOrderPrice(PurchaseOrder.PurchaseOrderID)
FROM PurchaseOrder

This works great for getting scalar values. When I want to return a set from the function as a table value function I cannot include it in my query as demonstrated above. I want to JOIN the results of two sets, a table and a table value function, which is not done in the SELECT clause; it is accomplished in the JOIN clause.

In this case, the PurchaseOrderID from the PurchaseOrder table is needed as a parameter for input into a table valued function. Let’s create a table valued function returning a sorted list of Purchase Order Details sorted by the line item number.

CREATE FUNCTION tfnGetPurchaseOrderDetails(@PurchaseOrderID INT)
RETURNS TABLE
AS
RETURN
SELECT *
FROM PurchaseOrderDetails
WHERE PurchaseOrderID = @PurchaseOrderID
ORDER BY LineitemNumber

But you can’t use the syntax

SELECT *
FROM PurchaseOrder
JOIN tfnPurchaseOrderTableStuff(PurchaseOrder.PurchaseOrderID).

Using CROSS APPLY I join the PurchaseOrder table results to the results of this table value function much in the same manner as I would join to another table or view. The main difference is that I now have a parameter specifying a filter for the results.

SELECT PurchaseOrder.*
,POD.*
FROM PurchaseOrder
CROSS APPLY tfmPurchaseOrderDetails(PurchaseOrder.PurchaseOrderID) POD

This performs very much like a CROSS JOIN, which is a Cartesian join. Every record from table A is joined to every record in table B. The difference with CROSS APPLY is that you are able to specify an input into the table function acting as table B, which may come from table A. It doesn’t have to work that way. But, it is nice that it does.

You see CROSS APPLY used many times with Data Management Views and Data Management Functions. This is a great way to master this fantastic capability.

Reader Feedback – Bsckward Compatible
Aaron:
I think that you’re missing one obvious recommendation:

this is the method I used a couple of times… Basically.. SSRS Express can do everything that normal SSRS can do (if you use Linked Servers).

Jac:
I think you may have left out one additional option: Leave the version of the DB alone and have them upgrade the SSRS instance to the new version, i.e. 2008r2. The SSRS reports will run against a db with a prior version. This involves possibly paying for a new SQL license for SSRS, but it does not require them to upgrade their DB and App, if that is the sticking point.

Do you have a favorite tip you would like to share? Send me an Emaiil with your idea at btaylor@sswug.org.

Cheers,

Ben

Featured Article(s)
Five Best Practices in Any-to-Cloud Data Integration
By using advanced data virtualization, and pushing back at the legacy monopoly attached to your wallet, you can speed up your time to implementation.

Featured Script
dba3_NorthWind_0050_Materialized_View_Invoices_Article
View it now! – Part II: Banishing view timeouts by implementing user "materialized views"… (read more)