SQL Server™ 2005 Bible

Chapter 22. Building User-Defined Functions

In This Chapter

• Creating scalar functions

• Replacing views with inline table-valued functions

• Using complex code within multi-statement table-valued functions to generate a result set

SQL Server 2000 introduced user-defined functions (UDFs), and the SQL Server community was initially slow to adopt them.

The community discovered that UDFs can be used to embed complex T-SQL logic within a query, and problems that were impossible or required cursors could now be solved with UDFs. The result is that UDFs have become a favorite tool in the toolbox of any serious SQL Server database developer.

The benefits of UDFs can be easily listed:

• UDFs can be used to embed complex logic within a query.

• UDFs can be used to create new functions for complex expressions.

• UDFs offer the benefits of views because they can be used within the from clause of a select statement or an expression, and they can be schema-bound. In addition, user-defined functions can accept parameters, whereas views cannot.

• UDFs offer the benefits of stored procedures because they are compiled and optimized in the same way.

The chief argument against developing with user-defined functions has to do with their portability.

UDFs are very proprietary, and any database that uses many UDFs will be difficult or impossible to port to another database platform without a significant redesign. As a result, any user-defined function must be rewritten as a view or stored procedure if the database must be ported ...