Posted by Erland Sommarskog on 03/01/06 00:38

Hugo Kornelis (hugo@perFact.REMOVETHIS.info.INVALID) writes:
> More efficient to call the function just once per row:
>
> SELECT Col1, SUM(Col2), MyResult
> FROM (SELECT Col1, Col2, MyFunc(Col3, Col4) AS MyResult
> FROM SomeTable) AS Der
> GROUP BY Col1, MyResult

I tried:

CREATE FUNCTION myfun (@int integer) RETURNS int AS
BEGIN
RETURN ((@int - 10000) % 43)
END
go
SELECT dbo.myfun(OrderID), COUNT(*)
FROM Northwind..Orders
GROUP BY dbo.myfun(OrderID)
go
SELECT myfun, COUNT(*)
FROM (SELECT myfun = dbo.myfun(OrderID)
FROM Northwind..Orders) AS x
GROUP BY myfun
go
DROP FUNCTION myfun

The plans look identical.

However, the derived table saves you from having to repeat a complex
expression, and this is a good thing.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]