x

Table-valued functions.

Seeder question:

In SQL 2005 and beyond, there is something called a table-valued function. Apparently there are a couple of different types of table-valued functions. What are they? What are the differences between them? When and how should I use either of them?

more ▼

asked Oct 25, 2009 at 11:42 PM in Default

Bob Hovious gravatar image

Bob Hovious
1.6k 5 6 9

Just thought it was worth noting that TVF's were available in 2000 too...
Nov 04, 2009 at 06:14 AM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

I break it down as single-statement table valued user defined functions, also called in-line, and multi-statement table valued user defined functions. The single statement table valued UDF's are effectively views with parameters. Execution of these UDF's will be incorporated directly into the execution plan with the rest of the associated query. Indexes may be used if the query within the UDF is well constructed and these are generally pretty safe constructs to use within your queries.

Multi-statement UDF's are built upon the structure of table variables. These are not incorporated within the rest of the execution plan and usually show up as a table scan against a table with one row. The problem is, your query may have more than one row. When the multi-statement UDF's are used on large sets of data they cause very serious performance problems. They won't scale well, and the performance issues are masked by the apparent low cost of the operations involved. You need to be extremely judicious in the use of multi-statement table valued UDF's.

more ▼

answered Oct 26, 2009 at 09:53 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
101k 19 21 74

(comments are locked)
10|1200 characters needed characters left

The two types are inline, and not inline. Many will list "multi-statement", but really the difference comes down to if you have "returns table as return(...)" (inline) or whether you use a table variable definition and use BEGIN ... END in your declaration.

The problem is that the system sees the BEGIN/END and runs the PROCEDURE in a different context. Without it, it takes the subquery in the return() call and can simplify it out.

Scalar functions cannot be inline, which causes problems with them.

Furthermore, non-inline functions do not contribute towards the cost reported by an execution plan. You need to use Profiler to see their effect.

more ▼

answered Oct 26, 2009 at 01:47 AM

Rob Farley gravatar image

Rob Farley
5.7k 16 18 20

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

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x249
x61
x57
x15

asked: Oct 25, 2009 at 11:42 PM

Seen: 4950 times

Last Updated: Oct 25, 2009 at 11:42 PM