SQL 你什么时候会使用表值函数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/40535519/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
When would you use a table-valued function?
提问by Jonathan Porter
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?
我试过阅读它和一些例子，但我仍然不清楚。有人可以解释或提供易于理解的用例场景吗？
采纳答案by Kittoes0124
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(max) = 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(max) = 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.GetClientswith 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).
内联表值函数特别棒，因为“内联”位可能是最好的解释here。这允许优化器以与它们封装的对象相同的方式处理这些函数，从而产生接近最佳的性能计划（假设您的索引和统计数据是理想的）。
回答by Alan Burstein
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 都返回一个表。mTVF 和 iTVF 之间的区别在于性能。简而言之 - 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/
我将 iTFV 用于常见的数据仓库查询，在这些查询中我需要一个接受参数和拆分/操作字符串的视图。改变我职业生涯的 iTVF 的更高级用法是用 iTVF 替换标量函数 - 请参阅 Jeff Moden 的这篇文章，标题为“如何使标量 UDF 运行得更快”：http://www.sqlservercentral.com/articles/T- SQL/91724/
- For simplicity I excluded the topic of CLR and other non T-SQL types of functions.
- 为简单起见，我排除了 CLR 和其他非 T-SQL 类型函数的主题。
