>>Would this ...
>>
>>select @startdate = dbo.myfunction(@startdate)
>>select @enddate = dbo.myfunction(@enddate)
>>
>>SELECT * from table where datefield between @startdate and @enddate
>>
>>be better than this...
>>
>>SELECT * from table where datefield between dbo.myfunction(@startdate) and dbo.myfunction(@enddate)
>
>No. From BOL as is:

Well, I reworked the inherited query - written by a college teacher. The code worked as it was, but it was slow and crashed if the user told it to process more than 30 days date range. Now it does 2 years worth of data in a blink.

>
>"User-defined Function Benefits
>The benefits of using user-defined functions in SQL Server are:
>
>They allow modular programming.

There must be overhead to locate the UDF and also to return a value. This holds true even in FoxPro. The following article shows a query without an external UDF running in 1.317 seconds.

http://zones.advisor.com/doc/17440

Then using a UDF it took 5.082 seconds and then a method of an object took 5.743 seconds. Having the snippet factory lookup a snippet by name, alter it and use macrosubstitution to execute the query took 1.619 seconds.

>
>You can create the function once, store it in the database, and call it any number of times in your program. User-defined functions can be modified independently of the program source code.

Considering how much faster this routine now runs without calling UDFs I tend to agree with Sergey.

>They allow faster execution.
>
>Similar to stored procedures, Transact-SQL user-defined functions reduce the compilation cost of Transact-SQL code by caching the plans and reusing them for repeated executions. This means the user-defined function does not need to be reparsed and reoptimized with each use resulting in much faster execution times.

That may be so, but doesn't hold true in this example. Multiple calls to the UDF took much more time than hard-coding the formula/joins.

>
>CLR functions offer significant performance advantage over Transact-SQL functions for computational tasks, string manipulation, and business logic. Transact-SQL functions are better suited for data-access intensive logic.
>
>
>They can reduce network traffic.

"Can" is not the same as "Do"

>
>An operation that filters data based on some complex constraint that cannot be expressed in a single scalar expression can be expressed as a function. The function can then invoked in the WHERE clause to reduce the number or rows sent to the client. "

Seems it depends on the nature of the function. In this case the UDF did a query that joined two tables. That must have been executed multiple times as the query that called the udf examined records. That was almost 1 million extra calls.