38

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?

Improve this question
2
  • possible duplicate of Functions vs Stored Procedures Commented Feb 23, 2014 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? Commented Feb 24, 2014 at 10:19

2 Answers 2

Reset to default
37

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 because of the inability to use SET and change state.

Improve this answer
Sign up to request clarification or add additional context in comments.

13 Comments

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: views also cache (like inline TVFs). Stored procs are just far more flexible (eg if I want SET NOCOUNT ON, SET XACTABORT + TRY/CATCH)
@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
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.
@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 :-)
|
1

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.

Improve this answer

1 Comment

One thing I noticed is I can't add "OPTIMIZE FOR UNKNOWN" to functions, which makes me think it is null and void.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.