Posted by Erland Sommarskog on 06/23/06 10:42

ujjc001 (ujjc001@gmail.com) writes:
> Given the following UDF, in sql 2000 can it be sped up, complied or
> anything of the like. A query returning 300,000 + rows times out when
> ran through the udf, inline case statements returns the rows in 5
> seconds.

Scalar UDFs can incur a large overhead in SQL 2000, and a lot of this
lies in the overhead for the call itself. So you are better off without
the function and using inline code.

In SQL 2005 you could write a UDF like this in C# or VB .Net, and you
could very well get performance than inline SQL. Overall, the overhead
cost for a call to a scalar UDF is lower in SQL 2005.

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