Tom Staab avatar image
Tom Staab asked

What are the advantages of each type of UDF?

SQL Server 2005/2008 supports 4 kinds of user defined functions:

  • T-SQL scalar function
  • T-SQL table-valued function
  • CLR scalar function
  • CLR table-valued function

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

1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Tom Staab avatar image Tom Staab ♦ commented ·
Bob's answer is excellent, but I'm curious about others' preferences. What is your favorite type of UDF and why?
0 Likes 0 ·
Bob Hovious avatar image
Bob Hovious answered

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.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

dave ballantyne avatar image
dave ballantyne answered

Just to pick up on the relative performance, i have simple example on my blog here.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.