?
Solved

Inline UDF Vs Table Valued UDF

Posted on 2004-11-29
2
Medium Priority
?
1,069 Views
Last Modified: 2008-01-09
Does an inline UDF return a virtual table (loaded into memory) in the same way as a table valued UDF?  Or is it more like a view that accepts parameters?

One advantage to using table valued over inline is that In my table definition I can specify a primary key and therefore make use of a clustered index.  I can't do that with an inline function.

In a scenario where I have a table of 10000 records and my udf is going to return an avg of 5 of those each time it's called would an inline function be more efficient than a table valued function?
0
Comment
Question by:billy21
2 Comments
 
LVL 16

Expert Comment

by:muzzy2003
ID: 12694929
The terminology is slighly confusing. These terms are used in the .NET IDE, but SQL Server Books Online has them slightly different. For table-valued function, they use "Multi-statement Table-valued Functions", and for inline function they use "Inline Table-valued Functions". Both return a table, and can be used inline in other SQL Statements. They can both be used like views that accept parameters, yes. The difference is that in what you are calling a table-valued function, you declare the table structure in the function declaration and then insert into it; in an inline function you just define the table using a single SELECT. In terms of how the table is returned, there is no difference.

You can always use a table-valued function where you would use an inline function, but the reverse does not hold. I would imagine (though I've seen no concrete information either way) that queries containing a table-valued function are easier for the query optimiser to optimise, as the table structure returned is explicitly given, whereas the inline function requires some further resolution to determine this. You are correct about the primary key, but if you're only returning an average of 5, I wonder if the overhead of creating the clustered index will outweigh any benefits that you reap from doing it this way. It really depends on the application. Try it both ways and measure the performance would be my advice.
0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 1000 total points
ID: 12696886
>> Does an inline UDF return a virtual table (loaded into memory) in the same way as a table valued UDF? <<

As always, SQL controls whether or not the result table is kept entirely in memory, but in general, if memory is available, both types will be in-memory tables (a table can be "virtual" and still be paged to disk/swap area as needed).


>> In a scenario where I have a table of 10000 records and my udf is going to return an avg of 5 of those each time it's called, would an inline function be more efficient than a table valued function? <<

Yes, almost certainly.  Just avoiding the overhead of declaring additional variables, etc., would make it more efficient.  And with only five, or so, rows returned, a clustered index isn't really needed.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question