|
|
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
Navigation:
[Reply to this message]
|