x

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

more ▼

asked Nov 07, 2009 at 01:16 AM in Default

Tom Staab gravatar image

Tom Staab
5.8k 6 8 10

Bob's answer is excellent, but I'm curious about others' preferences. What is your favorite type of UDF and why?
Nov 09, 2009 at 04:00 PM Tom Staab
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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.

more ▼

answered Nov 07, 2009 at 04:15 AM

Bob Hovious gravatar image

Bob Hovious
1.6k 5 6 9

Nice answer, Bob. ITVFs are my favorite too. As you said, they are like parameterized views. I am very fond of obscuring the underlying table structure from reporting front-ends, and I have found ITVFs to very helpful in this effort when used alongside views (particularly, indexed views when appropriate and possible).
Nov 07, 2009 at 11:38 AM Tom Staab
(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Nov 07, 2009 at 06:55 AM

dave ballantyne gravatar image

dave ballantyne
928 1 1 4

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x991
x58
x27
x15
x4

asked: Nov 07, 2009 at 01:16 AM

Seen: 3097 times

Last Updated: Nov 07, 2009 at 01:16 AM