Inline UDF Vs Table Valued UDF

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?
LVL 6
billy21Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

muzzy2003Commented:
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
Scott PletcherSenior DBACommented:
>> 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.