Tuesday 5 May 2009

Perform Join with table valued function in SQL server 2005

Using APPLY operator we can efficiently perform join operation with table returned by SQL able valued function. APPLY is new in sql server 2005.

It allows you to call a function-returning TABLE(Table Valued function) for each row of your outer TABLE. We can pass outer table's columns as function arguments.

We can have two types of APPLY Operations.

(1)CROSS APPLY.

this is much like Inner join .that means it will not return the outer tables row if function table have no row corresponding to it.

(2)OUTER APPLY.

this is much like Outerjoin .that means it will returns a NULL values if function table have no row corresponding to it.
Example:

My [Order] table have ID,CustomerID and OrderDate Fields.
My [OrderDetail] have ID,OrderID and ItemCount Fields.

Here is the Table valued function.

CREATE Function GetOrderDetailByOrderID(@OrderID int) returns tablereturn( select * from EC_OrderDetail where OrderID=@OrderID)

Below SQL Script With CROSS Apply.

Select * from EC_Order [ordertab] CROSS APPLYGetOrderDetailByOrderID(ordertab.ID) as OrderDetailorder by OrderDetail.OrderID

returns same result as Below SQL Script With Inner join

Select * from EC_Order [ordertab] inner joinEC_OrderDetail detail on(detail.orderId=ordertab.ID)order by detail.orderId

Follow this (http://www.codeproject.com/KB/database/APPLY_Operator.aspx ) as referance.

Thanks.