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

avatar image

Tom Staab ♦
14.5k 7 14 18

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

avatar image

Bob Hovious
1.6k 5 9 13

(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

avatar image

dave ballantyne
928 1 3 6

(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:

x1067
x68
x33
x17
x6

asked: Nov 07, 2009 at 01:16 AM

Seen: 3980 times

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

Copyright 2016 Redgate Software. Privacy Policy