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