Microsoft SQL Server 2012 Bible

Chapter 18

Building User-Defined Functions

In This Chapter

Creating Scalar Functions

Replacing Views with Inline Table-valued Functions

Using Complex Code within Multistatement, Table-valued Functions to Generate a Result Set

SQL Server 2000 introduced user-defined functions (UDFs), and adoption throughout the SQL Server community has, quite frankly, been spotty. In some cases, overuse and improper application of UDFs has given them a bad reputation as a performance killer. In other cases, UDFs have been implemented as a way to repeatedly enforce complex business rules through function logic, and co-exist peacefully with other T-SQL programming constructs. Your experience with functions will depend largely on your understanding of how they execute, their impact on performance, and when to use them. Functions can be a useful tool in your T-SQL toolbox when applied judiciously and thoughtfully.

Before digging into the different types of UDFs, consider the basics of what a function is. A UDF is a routine that accepts parameters, performs an action, and returns the result of that action. The result is either a scalar (single) value or a table, depending on how the function is defined.

The benefits of UDFs include:

• UDFs can embed complex logic within a query. UDFs can create new functions for complex expressions.
• 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. ...