表值函數 (TVF) 兩大類型

類型	語法特徵	優化器處理	效能特性
內聯 TVF (Inline TVF)	RETURNS TABLE ... RETURN SELECT ... 無 BEGIN/END 區塊	視圖化展開，併入外層查詢計畫，支援謂詞下推	極佳，等同直接寫 JOIN 子查詢
多陳述式 TVF (Multi-statement TVF)	RETURNS @TableVar TABLE ... BEGIN ... INSERT ... RETURN END	視為黑盒，估計固定 100 列 (舊版) 或 1 列 (新版)，無法展開	差，常導致嚴重誤估、巢狀迴圈過度查找

實測案例：AdventureWorks 環境

建立兩種 TVF

-- 多陳述式 TVF
CREATE FUNCTION dbo.tvf_multi_Test()
RETURNS @SaleDetail TABLE (ProductId INT)
AS BEGIN
    INSERT @SaleDetail
    SELECT ProductID
    FROM Sales.SalesOrderHeader soh
    JOIN Sales.SalesOrderDetail sod ON soh.SalesOrderID = sod.SalesOrderID;
    RETURN;
END;

-- 內聯 TVF
CREATE FUNCTION dbo.tvf_inline_Test()
RETURNS TABLE
AS RETURN
    SELECT ProductID
    FROM Sales.SalesOrderHeader soh
    JOIN Sales.SalesOrderDetail sod ON soh.SalesOrderID = sod.SalesOrderID;

相同 JOIN 查詢，截然不同計畫

-- 多陳述式 TVF JOIN
SELECT c.PersonID, p.Name, COUNT(*) AS Cnt
FROM Person.BusinessEntityContact c
JOIN dbo.tvf_multi_Test() t ON c.PersonID = t.ProductId
JOIN Production.Product p ON t.ProductId = p.ProductID
GROUP BY c.PersonID, p.Name;

-- 內聯 TVF JOIN
SELECT c.PersonID, p.Name, COUNT(*) AS Cnt
FROM Person.BusinessEntityContact c
JOIN dbo.tvf_inline_Test() t ON c.PersonID = t.ProductId
JOIN Production.Product p ON t.ProductId = p.ProductID
GROUP BY c.PersonID, p.Name;

執行計畫成本對比

• 多陳述式 TVF：優化器誤估基數，對 BusinessEntityContact 選擇 121,317 次 Key Lookup (Nested Loops)，實際 I/O 極大。
• 內聯 TVF：優化器展開內部 Join，正確選擇 Hash Match / Scan，I/O 降低數量級。

根本原因分析

1. 基數估計失準：多陳述式 TVF 無統計資料，優化器使用硬編碼猜測 (舊版 100 列，新版 1 列)。
2. 無法謂詞下推：外層 WHERE / JOIN 條件無法推入 TVF 內部，導致 TVF 全量掃描後再過濾。
3. 無法利用索引：TVF 回傳的表變數無統計資料，Join 時無法產生最佳存取路徑。

最佳實務建議

1. 能用內聯 TVF 就用內聯 TVF：單一 SELECT 邏輯務必改寫為 Inline。
2. 若邏輯複雜需多步驟 → 改用 預存程序 + 暫存表 / 表變數，由呼叫端控制流程。
3. 若必須用多陳述式 TVF → 考慮加入 OPTION (RECOMPILE) 或 USE HINT('DISABLE_OPTIMIZER_ROWGOAL') 緩解，但治標不治本。
4. SQL Server 2019+ 標量 UDF 內聯化 功能可部分緩解標量函數問題，但不適用於多陳述式 TVF。

結語

「表值函數好用，但多陳述式 TVF 是效能殺手」。開發審查時，凡見 RETURNS @Table TABLE ... BEGIN ... END 即應要求重構為內聯 TVF 或預存程序。