Posted by Erland Sommarskog on 10/01/48 11:42

(jannoergaard@hotmail.com) writes:
> Thank you very much. I modified your query a lillte bit, and it worked
> as wanted in the function.
>
> CREATE FUNCTION GuruInvoicedPeriodNew (@AccountNum as
> VarChar(10),@Period AS Int, @ThisY as DateTime)
> RETURNS Float AS
> BEGIN

Yellow alert! How are you going to use this function? If you are going
to say something like:

SELECT AccountNum, dbo.InvoicedPeriod(AccountNum, 1, getdate()),
dbo.InvoicedPeriod(AccountNum, 0, getdate())
FROM accounts

It's not going to perform well. Scalar UDFs is something you should use
with care, and not the least scalar UDFs that perform table access.
There is quite an overhead for calling a UDF once per row, and when you
do table access, you have essentially created a disguised cursor.

--
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]