I've bench marked the type of scalar function in Adam's article against correlated sub queries, since they're doing the same type of thing, and they seem to work at about the same efficiency. When the result set is small you don't see much performance problem. When the result set gets into the hundreds or thousands of rows, the performance takes a big dive.

I've pretty much always used derived tables when I need to compare a single row to an aggregate value and I usually get good performance.

Something like this:

SELECT

O.CustomerID, OD.ProductID, OD.Quantity

, X.MaxQty

FROM

Orders O

INNER JOIN

OrderDetails OD ON

O.OrderID = OD.OrderID

INNER JOIN

(SELECT ProductID, MAX(Quantity) AS MaxQty

FROM Orders O

INNER JOIN OrderDetails OD ON

O.OrderID = OD.OrderID

WHERE O.OrderDate BETWEEN '1/1/07' and '1/31/07'

GROUP BY OD.ProductID

) AS X ON

OD.ProductID = X.ProductID

WHERE

O.OrderID BETWEEN '1/1/07' AND '1/31/07'

The hard dates are usually just variables in the SProc.

I'd have to bench mark a UDF that returns a table against this type of logic to see what happens

Todd Fifield