表值函數 (TVF) 分為內聯表值函數 (iTVF) 與多語句表值函數 (mTVF)。前者像視圖可參與查詢優化,後者是「黑盒」導致嚴重效能退化。本文透過 AdventureWorks 實測對比兩者 Join 效能差異,分析根因並給出檢測與重構指南。
| 特性 | 內聯 TVF (iTVF) | 多語句 TVF (mTVF) |
|---|---|---|
| 語法 | RETURNS TABLE AS RETURN SELECT ... | RETURNS @t TABLE(...) BEGIN ... INSERT @t ... RETURN |
| 優化器可見度 | 完整展開,參與代數樹優化 | 黑盒,僅知行數估計(預設 1 行/100 行) |
| 索引/統計資訊 | 可見底層表索引、統計 | 不可見,假設極小結果集 |
| 謂詞下推 | 支援 (WHERE 條件下推) | 不支援 |
| 平行處理 | 支援 | 不支援 |
| 適用場景 | 單一 SELECT、參數化視圖 | 複雜邏輯、臨時表、動態 SQL、游標 |
-- 多語句 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;
-- mTVF Join
SELECT c.PersonID, p.Name, COUNT(*) AS [number of unit]
FROM Person.BusinessEntityContact c
JOIN dbo.tvf_multi_Test() tst ON c.PersonID = tst.ProductId
JOIN Production.Product p ON tst.ProductId = p.ProductID
GROUP BY c.PersonID, p.Name;
-- iTVF Join
SELECT c.PersonID, p.Name, COUNT(*) AS [number of unit]
FROM Person.BusinessEntityContact c
JOIN dbo.tvf_inline_Test() tst ON c.PersonID = tst.ProductId
JOIN Production.Product p ON tst.ProductId = p.ProductID
GROUP BY c.PersonID, p.Name;

BusinessEntityContact 選擇 **121,317 次 Key Lookup**(Nested Loops),而非單次 ScanSalesOrderDetail 索引-- 利用查詢存儲/計畫快取掃描
WITH XMLNAMESPACES(DEFAULT 'https://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT
qs.execution_count,
qp.query_plan.value('(//@EstimateRows)[1]', 'float') AS est_rows,
qp.query_plan.value('(//@ActualRows)[1]', 'float') AS actual_rows,
OBJECT_NAME(qp.objectid) AS proc_name,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qp.query_plan.exist('//TableValuedFunction') = 1
AND qp.query_plan.value('(//TableValuedFunction/@FunctionName)[1]', 'nvarchar(128)')
NOT LIKE 'tvf_inline_%'; -- 排除已知 iTVF
RETURNS TABLE AS RETURN SELECT ...CASE / IIF / UNION ALLsp_executesql 返回結果集-- CROSS APPLY (內連接語義)
SELECT c.*, ca.*
FROM Person.Contact c
CROSS APPLY dbo.tvf_GetContactPhones(c.ContactID) ca;
-- OUTER APPLY (左外連接語義)
SELECT c.*, ca.*
FROM Person.Contact c
OUTER APPLY dbo.tvf_GetContactPhones(c.ContactID) ca;
APPLY 允許右側表值函數引用左側資料行,**逐行調用**,是將行集邏輯側向化為集合操作的關鍵。
itvf_ 前綴,mTVF 嚴禁用於高頻查詢mTVF 實體化 → 索引 → 視圖/SP 消費表值函數是雙面刃:iTVF 是「參數化視圖」,零開銷;mTVF 是「黑盒程式」,Join 時極易釀災。架構審查時,**任何出現在 FROM/JOIN/APPLY 中的 mTVF 都是紅旗**,優先重構或實體化。