APPLY Table Operator in SQL Server 2005
APPLY Table Operator is available in SQL Server 2005 and later editions. This evaluates the right hand-side table expression to left hand-side table expression. Other way, it correlates each row of left-hand side table expression to right hand-side table expression.
Let’s try to make it clear by coding. You can use NorthWind, AdventureWorks or download the database here to run those scripts.
CREATE FUNCTION udf_Top_Products(@sup_id as int, @cat_id as int, @no as int) RETURNS TABLE AS RETURN SELECT TOP(@no) WITH TIES ProductID,ProductName,UnitPrice FROM Products WHERE SupplierID = @sup_id AND CategoryID = @cat_id ORDER BY UnitPrice DESC
This table valued function udf_Top_Products takes three arguments and returns a set of rows.
SELECT * FROM dbo.udf_Top_Products(1,1,2)
This query returns top 2 products in terms of unit price for Supplier ID 1 and Category ID 1. What if you want to get top 2 products for Category ID 1 and for each Supplier ID, APPLY Operator is here to do this efficiently.
SELECT CompanyName, ProductID, ProductName, UnitPrice FROM Suppliers AS S CROSS APPLY dbo.udf_Top_Products(S.SupplierID,1,2)
CROSS APPLY eliminates rows that returns NULL in right-hand side table expression.
SELECT CompanyName, ProductID, ProductName, UnitPrice FROM Suppliers AS S OUTER APPLY dbo.udf_Top_Products(S.SupplierID,1,2)
On the other hand, OUTER APPLY returns all of the rows.
A Rahim Khan