Microsoft® SQL Server® 2008 Bible

Chapter 25. 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. Nevertheless, UDFs were my personal favorite new feature in SQL Server 2000, and I still use them frequently.

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. This is huge. I've solved several nasty problems using user-defined functions.

• 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 potential performance issues if they're misused. Any function, user-defined or system that must be executed ...