SQL Server 2005/2008 supports 4 kinds of user defined functions:
What are the advantages of each type? What are the disadvantages? Can you give some examples of situations when each would be the "best" choice?
Finally, what is your favorite type, and why? I know that's a subjective question, but I think the answers can still be helpful for anyone new to UDFs (or new to a particular type).
I will start by expanding and reorganizing the set slightly
* T-SQL scalar function
* CLR scalar function
* T-SQL table-valued functions
- Multiline Table-valued functions
- CLR table-valued function
- Inline table valued functions
Scalar functions are the traditional functions, which return a single value. They are fine for use with transactions involving individual rows, but can become a performance bottleneck if used in queries with large result sets... ESPECIALLY if the functions contain queries, and not just calculations or string manipulations.
CLR Scalar functions exit traditional SQL to routines written procedural languages that are superior to SQL for such things as string manipulations, or for calculating results (such as running totals) that are done more efficiently with procedural techniques than with set-based T-SQL approaches. They may also be used to give SQL access to information outside the database.
Table-valued functions return result sets with one or more columns and one or more rows.
Multiline table-valued functions can be very handy for procedurally producing results based on a number of columns, where Case Expressions would become incredibly complex and unreadable. However, multiline table-valued functions can also be a burden to performance when applied to large result sets.
Inline CLR functions have applications for parsing rows into columns, returning results from file subsystems, or any other areas where .NET coding is superior to T-SQL. I suspect their performance to be as burdensome as Multiline table-valued functions, but I have no statistics to back that assertion up. (Would anyone with hard numbers please comment on this?)
Finally, inline table-valued functions are essentially parameterized views. Like views, but unlike other functions, ITVFs are used by the optimizer to produce what is hopefully an efficient query plan. Like other table-valued functions, ITVFs can return a result set consisting of multiple rows and multiple columns. Unlike other table-valued functions, ITVFs must consist of a single query without any other statement, such as declaration of local variables. ITVFs have become my personal favorites because of their speed when CROSS APPLY'ed to large result sets.
