SQL Server：表值函数与存储过程 [英] SQL Server: Table-valued Functions vs. Stored Procedures

问题描述

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

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