本文介绍了SQL Server:表值函数与存储过程的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我一直在大量阅读执行计划和存储过程中动态参数的问题。我知道推荐的解决方案。



然而,我的问题是我读过的所有内容都表明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:表值函数与存储过程的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

06-18 20:11