453
CHAPTER
18
Building User-Defi ned Functions
IN THIS CHAPTER
Creating Scalar FunctionsReplacing Views with Inline Table-valued FunctionsUsing Complex Code within Multistatement, Table-valued Functions to Generate a Result SetS
QL Server 2000 introduced user-defi ned 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 func-
tion 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 perfor-
mance, 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 defi ned.The benefi ts 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-defi ned functions can accept parameters. UDFs
can help enforce consistency and reusability when a complex calculation must be applied
throughout an application.The chief argument against developing with user-defi ned functions has to do with potential perfor-
mance issues if they’re misused. Any function, user-defi ned or system, that must be executed for
every row in a WHERE clause will cripple performance.c18.indd 453c18.indd 453 7/30/2012 5:41:40 PM7/30/2012 5:41:40 PM
http://www.it-ebooks.info