Microsoft SQL Server 2012 Bible

Best Practices with User-Defined Functions

Although user-defined functions add flexibility to your T-SQL options, there are some serious performance drawbacks to the improper application of them. They are not a standard replacement for subqueries, views, or stored procedures. In some cases, the user-defined function provides benefits that make a strong case for its use, such as parameterization or reusability. The trick is to use them properly.

Maximizing Performance

From a query optimizer perspective, different types of user-defined functions are handled differently. You've probably noticed that the examples provided for each of the three types of UDF, scalar, inline, and table-valued, all produced essentially the same result set. This was by design so that you could illustrate that in quite a few cases; you can define your function as any one of the three types. If you choose to use a user-defined function to encapsulate your query logic, follow these basic guidelines:

• Choose inline table-valued functions over multistatement table-valued functions whenever possible.
• Even if it looks like you need a scalar function, write it as an inline table-valued function; avoid scalar functions wherever possible.
• If you need a multistatement table-valued function, check to see if a stored procedure might be the appropriate solution. This might require a broader look at query structure, but it's worth taking the time to do it.

Using Consistent Naming Conventions

There are few things more ...