下面举几个例子，以防万一:
内联表值
CREATE FUNCTION MyNS.GetUnshippedOrders()
RETURNS TABLE
AS
RETURN SELECT a.SaleId, a.CustomerID, b.Qty
FROM Sales.Sales a INNER JOIN Sales.SaleDetail b
ON a.SaleId = b.SaleId
INNER JOIN Production.Product c ON b.ProductID = c.ProductID
WHERE a.ShipDate IS NULL
GO
多语句表取值
CREATE FUNCTION MyNS.GetLastShipped(@CustomerID INT)
RETURNS @CustomerOrder TABLE
(SaleOrderID INT NOT NULL,
CustomerID INT NOT NULL,
OrderDate DATETIME NOT NULL,
OrderQty INT NOT NULL)
AS
BEGIN
DECLARE @MaxDate DATETIME
SELECT @MaxDate = MAX(OrderDate)
FROM Sales.SalesOrderHeader
WHERE CustomerID = @CustomerID
INSERT @CustomerOrder
SELECT a.SalesOrderID, a.CustomerID, a.OrderDate, b.OrderQty
FROM Sales.SalesOrderHeader a INNER JOIN Sales.SalesOrderHeader b
ON a.SalesOrderID = b.SalesOrderID
INNER JOIN Production.Product c ON b.ProductID = c.ProductID
WHERE a.OrderDate = @MaxDate
AND a.CustomerID = @CustomerID
RETURN
END
GO
使用一种类型(内联或多语句)是否比另一种有优势?是否存在一个比另一个更好的情况，或者这种差异纯粹是句法上的?我意识到这两个示例查询正在做不同的事情，但有一个原因我将以这种方式编写它们吗?
读到它们，它们的优点/区别并没有真正被解释清楚。
使用多行函数的另一种情况是避免sql server下推where子句。
For example, I have a table with a table names and some table names are formatted like C05_2019 and C12_2018 and and all tables formatted that way have the same schema. I wanted to merge all that data into one table and parse out 05 and 12 to a CompNo column and 2018,2019 into a year column. However, there are other tables like ACA_StupidTable which I cannot extract CompNo and CompYr and would get a conversion error if I tried. So, my query was in two part, an inner query that returned only tables formatted like 'C_______' then the outer query did a sub-string and int conversion. ie Cast(Substring(2, 2) as int) as CompNo. All looks good except that sql server decided to put my Cast function before the results were filtered and so I get a mind scrambling conversion error. A multi statement table function may prevent that from happening, since it is basically a "new" table.
在内部，SQL Server像对待视图一样对待内联表值函数，像对待存储过程一样对待多语句表值函数。
当内联表值函数作为外部查询的一部分使用时，查询处理器将扩展UDF定义，并使用这些对象上的索引生成访问底层对象的执行计划。
For a multi-statement table valued function, an execution plan is created for the function itself and stored in the execution plan cache (once the function has been executed the first time). If multi-statement table valued functions are used as part of larger queries then the optimiser does not know what the function returns, and so makes some standard assumptions - in effect it assumes that the function will return a single row, and that the returns of the function will be accessed by using a table scan against a table with a single row.
当多语句表值函数返回大量行并在外部查询中连接时，它们的性能可能会很差。性能问题主要是由于这样一个事实:优化器将生成一个假设返回一行的计划，而这个计划不一定是最合适的计划。
根据经验，我们发现，由于这些潜在的性能问题，在可能的情况下，应该优先使用内联表值函数而不是多语句函数(当UDF将被用作外部查询的一部分时)。
如果你要做一个查询，你可以在你的内联表值函数中加入:
SELECT
a.*,b.*
FROM AAAA a
INNER JOIN MyNS.GetUnshippedOrders() b ON a.z=b.z
它将产生很少的开销，并且运行良好。
如果你试图在类似的查询中使用你的多语句表值，你会有性能问题:
SELECT
x.a,x.b,x.c,(SELECT OrderQty FROM MyNS.GetLastShipped(x.CustomerID)) AS Qty
FROM xxxx x
由于您将对返回的每一行执行函数1次，因此随着结果集变得越来越大，它将运行得越来越慢。
