问题描述
我一直在大量阅读执行计划和存储过程中动态参数的问题。我知道推荐的解决方案。
然而,我的问题是我读过的所有内容都表明SQL Server缓存存储过程的执行计划。没有提到表值函数。我假设它对于Views(出于兴趣)是这样。
它每次调用Table-value函数时是否重新编译?
何时最好使用表值函数而不是存储过程? 解决方案
我一直在大量阅读执行计划和存储过程中动态参数的问题。我知道推荐的解决方案。
然而,我的问题是我读过的所有内容都表明SQL Server缓存存储过程的执行计划。没有提到表值函数。我假设它对于Views(出于兴趣)是这样。
它每次调用Table-value函数时是否重新编译?
何时最好使用表值函数而不是存储过程? 解决方案
内联表值函数(TVF)就像一个宏:它被扩展到外部查询中。它没有这样的计划:调用SQL有一个计划。
多语句TVF有一个计划(将会找到一个引用)。
当您想要改变参数化输入的SELECT列表时,TVF很有用。内联TVF被扩展,并且外部选择/哪里将被优化器考虑。对于多语句TVF,优化不是真的可行,因为它必须运行完成,然后过滤。
就个人而言,我会使用存储通过多语句TVF处理。他们更灵活(例如提示,可以改变状态,SET NOCOUNT ON,SET XACTABORT等)。
我不反对内联TVF,但不倾向于使用他们面向客户端的代码,因为无法使用SET和更改状态。
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?
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.
这篇关于SQL Server:表值函数与存储过程的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!