Hugo Kornelis (hu**@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,
es****@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx