SQL Server: Table-valued Functions vs. Stored Procedures

Question

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?

Answer 1

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.

Answer 2

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.