Transact SQL User-Defined Functions in SQL Server 2005

Transact SQL User-Defined Functions in SQL Server 2005

SQL Server 2005 supports three types of functions: scalar, table functions, and aggregate functions. User-defined scalar functions return a single data value of the type defined in the RETURNS clause. User-defined table-valued functions return a table data type. Aggregate functions perform a calculation on a set of values and return a single value. Examples are COUNT, SUM, MIN, and MAX.

Performance Implications
Using UDFs will impact the performance of queries. The extent of the performance impact depends upon how and where you use a user-defined function. This is also true of built-in functions. However, UDFs have the potential for more dramatic performance hits than built-in functions. You should exercise caution when implementing functions in your queries and perform benchmarking tests to insure that the benefits of using your functions exceed the performance costs of using them.

For a complete comparision record, please navigate to the following URL:
User Defined Function performance comparison
http://sqlblogcasts.com/blogs/simons/archive/2005/01/17/User-Defined-Function-performance-comparison.aspx