Take the 2-minute tour ×

I have been doing a lot of reading up on execution plans and the problems of dynamic parameters in stored procedures. I know the suggested solutions for this.

My question, though, is everything I have read indicated that SQL Server caches the execution plan for stored procedures. No mention is made of Table-value functions. I assume it does so for Views (out of interest).

Does it recompile each time a Table-value function is called?

When is it best to use a Table-value function as opposed to a stored procedure?

share|improve this question
possible duplicate of Functions vs Stored Procedures –  Gert Arnold Feb 23 at 12:04
This question was asked and answered 3 years ago. What's the purpose of closing it know even if it is a duplicate as alleged? –  IanC Feb 24 at 10:19
add comment

3 Answers

up vote 10 down vote accepted

An inline table valued function (TVF) is like a macro: it's expanded into the outer query. It has no plan as such: the calling SQL has a plan.

A multi-statement TVF has a plan (will find a reference).

TVFs are useful where you want to vary the SELECT list for a parameterised input. Inline TVFs are expanded and the outer select/where will be considered by the optimiser. For multi-statement TVFs optimisation is not really possible because it must run to completion, then filter.

Personally, I'd use a stored proc over a multi-statement TVF. They are more flexible (eg hints, can change state, SET NOCOUNT ON, SET XACTABORT etc).

I have no objection to inline TVFs but don't tend to use them for client facing code becuase of the inability to use SET and change state.

share|improve this answer
Wow, so the bottom line is only stored procedures cache execution plans, and are therefore the most efficient method of querying data, beating even views. –  IanC Nov 23 '10 at 12:43
@IanC: views also cache (like inline TVFs). Stored procs are just far more flexible (eg if I want SET NOCOUNT ON, SET XACTABORT + TRY/CATCH) –  gbn Nov 23 '10 at 12:49
@IanC: The OPTION hint may be ignored. Multi-statement TVFs are notorious black boxes: I'm not sure it will propogate into the TVF. Links: One, Two –  gbn Nov 23 '10 at 13:38
What we mean by black boxes is this: your tvf returns 20 columns. You do SELECT col1, col2 from tvf WHERE foo = bar. An inline TVF (or a stored proc that has only col1 and col2) will work out the plan for col1, col2 with the where. A multi TVF has to run for all 20 columns, spool results, then filter, then restrict to 2 columns. –  gbn Nov 24 '10 at 6:49
@IanC: The "OPTION OPTIMIZE FOR ? UNKNOWN" would go in the outer query that uses in the inline TVF (This applies to most hints BTW). All plans are equal too whether stored proc or ad-hoc batch or a view using an inline TVFs. Scalar: if the scalar function does table access then it's just as bad as a multi TVF. It probably has a plan but I can't remember now :-) –  gbn Nov 24 '10 at 10:20
show 7 more comments

Recently, I had a need to populate the salesperson's manager1, 2 and 3 - that is - his manager's manager and so on up to 3 levels in the salesperson table.

My first thought was to populate manager1 first and then use 2 different update statements to update manager 2 and 3. I felt bored to do that. Nor did I want to write a recursive query. I wanted to try something different and this is what I did:

I created a Table Valued (multi line) function even though it returned just 1 row - to return the managerid and managername for the emplid passed and used the function twice in the update statement.

All this was for testing purposes only and I honestly expected my query to perform badly. The salesperson table has 5500 records while the hr_view has 450,000 records.

  1. The function fn_getmanager(emplid) returns a single record - the manager_emplid and manager_name from hr_view.

  2. update sp
         manager1 = v1.manager_name,
         manager2 = m2.manager_name,
         manager3 = m3.manager_name
         salesperson sp,
         hr_view v1,
         fn_getmanager(v1.manager_emplid) m2,
         fn_getmanager(m2.manager_emplid) m3
         sp.emplid = v1.emplid

  3. The update ran in 1 second. I exploded the salesperson table from 5000 to 1.2 million records and the update executed in 35 seconds.

  4. I changed the multi line to an inline Table valued function and it took more than 2 minutes and I canceled the query.

Since it is recursion that I need ie since my function calls must return different values for every row and I really am not sure if I can benefit from doing a recursive query and populating a temp table or something. The row counts of my tables are not bound to increase in numbers any time soon PLUS I really liked the way my update statement reads - it is very clean.

MY QUESTION - why is the multi line TVF faster than inline? Is it because the function is used in the FROM clause and that they are not used in any joins? Should i really change the way the code is written so that the performance doesn't falter in the long run?

share|improve this answer
Post the execution plans for both. –  IanC Jan 13 '11 at 13:57
add comment

I haven't verified this, but I take for granted that the execution plan for functions are also cached. I can't see a reason why that would not be possible.

The execution plan for views are however not cached. The query in the view will be part of the query that uses the view, so the execution plan can be cached for the query that uses the view, but not for the view itself.

The use of functions versus stored procedured depends on what result you need from it. A table-valued function can return a single result, while a stored procedure can return one result, many results, or no result at all.

share|improve this answer
One thing I noticed is I can't add "OPTIMIZE FOR UNKNOWN" to functions, which makes me think it is null and void. –  IanC Nov 23 '10 at 10:22
add comment

Your Answer


