SQL Server：表值函数与存储过程
我一直在阅读大量有关执行计划和存储过程中动态参数问题的文章。我知道为此建议的解决方案。
但我的问题是，我读到的所有内容都表明 SQL Server 缓存了存储过程的执行计划。没有提及表值函数。我认为它对于视图是这样做的（出于兴趣）。
每次调用表值函数时都会重新编译吗？
什么时候最好使用表值函数而不是存储过程？
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
内联表值函数 (TVF) 就像宏：它扩展到外部查询中。它没有这样的计划：调用 SQL 有一个计划。
多语句TVF有一个计划（会找到参考）。
当您想要更改参数化输入的 SELECT 列表时，TVF 非常有用。内联 TVF 已扩展，优化器将考虑外部 select/where。对于多语句 TVF 优化实际上是不可能的，因为它必须运行到完成，然后进行过滤。
就我个人而言，我会使用存储过程而不是多语句 TVF。它们更加灵活（例如提示、可以更改状态、SET NOCOUNT ON、SET XACTABORT 等）。
我不反对内联 TVF，但不倾向于将它们用于面向客户端的代码，因为无法使用 SET 和更改状态。
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.
我还没有验证这一点，但我想当然地认为函数的执行计划也被缓存了。我看不出为什么这是不可能的。
但是，视图的执行计划不会被缓存。视图中的查询将成为使用该视图的查询的一部分，因此可以为使用该视图的查询缓存执行计划，但不能为视图本身缓存。
使用函数与存储过程取决于您需要从中得到什么结果。表值函数可以返回单个结果，而存储过程可以返回一个结果、多个结果或根本不返回结果。
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.