什么时候使用表值函数?

I'm currently learning about functions in sql server and I don't understand why/when you would use an inline table valued function.

我目前正在学习sql server中的函数，我不明白为什么/什么时候使用内联表值函数。

I've tried reading about it and some examples but it is still unclear to me. Can someone explain or provide an easy to understand use-case scenario?

我试着读过一些例子，但我还不清楚。有人能解释或提供一个容易理解的用例场景吗?

2 个解决方案

#1

Table-valued functions are "just" parameterized views. This makes them extremely powerful for encapsulating logic that would otherwise be hidden behind an opaque stored procedure. Here's an example:

表值函数是“仅”参数化视图。这使得它们对于封装逻辑非常强大，否则这些逻辑将隐藏在不透明的存储过程后面。这里有一个例子:

Inline Table-valued Function:

内联表值函数:

create function dbo.GetClients (
    @clientName nvarchar(50) = null
)
returns table
return (
    select *
    from dbo.Clients as a
    where ((a.ClientName = @clientName) or a.ClientName is null)
);

Stored Procedure:

存储过程:

create procedure dbo.usp_GetClients (
    @clientName nvarchar(50) = null
)
as
begin;
    select *
    from dbo.Clients as a
    where ((a.ClientName = @clientName) or a.ClientName is null)
end;

Unlike the stored procedure call, a table-valued function allows me to compose the logic from dbo.GetClients with other objects:

与存储过程调用不同，表值函数允许我从dbo编写逻辑。GetClients与其他对象:

select *
from dbo.GetClients(N'ACME') as a
join ... as b
    on a.ClientId = b.ClientId

In such situations I cannot imagine using a stored procedure because of how restrictive it is when compared to the table-valued function. I would be forced to marshal the data around myself using a temp table, table variable, or application layer in order to combine results from multiple objects.

在这种情况下，我无法想象使用存储过程，因为与表值函数相比，存储过程有多么严格。我将*使用临时表、表变量或应用层对周围的数据进行编组，以便合并来自多个对象的结果。

Inline table-valued functions are especially awesome because of the "inline" bit which is probably best explained here. This allows the optimizer to treat such functions no differently than the objects they encapsulate, resulting in near optimal performance plans (assuming that your indexes and statistics are ideal).

内联表值函数特别棒，因为“内联”位可能是这里最好的解释。这允许优化器对这些函数的处理与它们封装的对象没有区别，从而导致接近最优的性能计划(假设您的索引和统计数据是理想的)。

#2

This is a great question and a topic that's not discussed enough IMHO. Think of inline table valued functions as views that accept parameters. That's the short answer but let's dig a little deeper...

这是一个很好的问题，也是一个没有被充分讨论的话题。可以将内联表值函数看作接受参数的视图。这是简短的回答，但让我们再深入一点……

In SQL server you have three kinds of user-defined functions*: scalar functions (svf), multi-line table valued functions (mTVF) and inline table valued functions (iTVF). svfs return a single value, both mTVFs and iTVFs return a table. The difference between mTVFs and iTVFs is performance. In short - mTVFs are slow, iTVFs can be (and almost always are) much faster. mTVFs allow you to do things you couldn't do in a view (e.g. create temp tables, perform loops, utilize cursors...), iTVFs, again, have the same restrictions as views except for they can except parameters.

在SQL server中，有三种用户定义函数*:标量函数(svf)、多行表值函数(mTVF)和内联表值函数(iTVF)。svfs返回单个值，mTVFs和iTVFs都返回一个表。mTVFs和iTVFs的区别在于性能。简而言之，mTVFs很慢，iTVFs可以(而且几乎总是)快得多。mTVFs允许您做在视图中不能做的事情(例如创建临时表、执行循环、使用游标……)，iTVFs与视图具有相同的限制，除了可以除参数之外。

I use iTFVs for common data warehouse queries where I need a view that takes parameter and splitting/manipulating strings. A more advanced use of iTVFs which has changed my career is replacing scalar functions with iTVFs - see this article from Jeff Moden titled, "How to Make Scalar UDFs Run Faster": http://www.sqlservercentral.com/articles/T-SQL/91724/

我将iTFVs用于常见的数据仓库查询，在这些查询中，我需要一个接受参数和分割/操作字符串的视图。iTVFs的一个更高级的用途是用iTVFs替换标量函数

For simplicity I excluded the topic of CLR and other non T-SQL types of functions.
为了简单起见，我排除了CLR和其他非T-SQL类型函数的主题。

#1