萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> 資料庫綜合

SQL Server 自訂函數 vs 預存程序:核心差異、適用場景與選型指南

開發人員常在「該寫函數還是預存程序?」猶豫。本文從回傳型別、副作用限制、呼叫方式、效能特性、安全性五大維度深度對比,並給出明確的選型決策樹。

核心差異對照表

維度自訂函數 (UDF)預存程序 (Stored Procedure)
回傳值**必須**回傳標量值或表變數 (TABLE)。
標量函數:單一值。
表值函數:結果集 (可像資料表 JOIN)。
可回傳整數狀態碼 (RETURN @int),**主要透過 SELECT/OUTPUT 參數/結果集** 回傳資料。可回傳多個結果集。
參數模式僅支援 IN (輸入) 參數。
**無 OUTPUT 參數**。
支援 IN / OUT / INOUT 參數,
可回傳多個輸出值。
副作用 (Side Effects)**嚴格禁止**:
• 不得修改資料表 (INSERT/UPDATE/DELETE)
• 不得呼叫非確定性函數 (GETDATE, NEWID, RAND)
• 不得使用暫存表/動態 SQL
• 不得變更資料庫狀態
**完全支援**:
• DML/DDL 操作
• 交易控制 (BEGIN/COMMIT/ROLLBACK)
• 動態 SQL、暫存表、游標
• 呼叫擴展預存程序、CLR
呼叫方式**內嵌於查詢中**:
SELECT dbo.fn_GetName(1);
SELECT * FROM dbo.fn_GetOrders(1); (表值函數)
**獨立執行**:
EXEC dbo.usp_GetOrders @CustID=1;
或 EXEC @rc = dbo.usp_DoWork @p1, @p2 OUT;
錯誤處理無法使用 TRY...CATCH、RAISERROR、THROW。完整支援 TRY...CATCH、THROW、自訂錯誤訊息、交易回滾。
計畫快取標量函數常導致逐列執行(RBAR),嚴重拖慢效能。
內聯表值函數 (iTVF) 會內展至外層計畫,**效能最佳**。
獨立編譯計畫,參數嗅探可預測。
可用 OPTION (RECOMPILE)、OPTIMIZE FOR 微調。
權限管理授與 EXECUTE 權限即可呼叫,
底層物件權限由擁有者鏈決定。
同樣授與 EXECUTE,
但可在程序內執行呼叫者無權限的操作 (所有權鏈/模組簽署)。
除錯難以單步除錯,需包裝在查詢中。SSMS 完整支援中斷點、變數監看、呼叫堆疊。

三種函數類型的效能階梯

  1. 內聯表值函數 (iTVF):RETURNS TABLE AS RETURN SELECT ... 無 BEGIN...END。優化器**內展**為查詢片段,**零額外開銷**,可並行、可用索引。✅ **首選**。
  2. 多語句表值函數 (mTVF):RETURNS @t TABLE ... BEGIN ... INSERT @t ... RETURN。優化器視為黑盒,**估計固定 100 列**,常導致嚴重低估/高估,無法並行。⚠️ 慎用。
  3. 標量函數 (SF):回傳單一值。**逐列執行 (RBAR)**,每列呼叫一次函數,CPU 暴增,阻礙並行。SQL 2019+ 有標量函數內聯功能可自動優化部分簡單函數。❌ 大量資料避免使用。

選型決策樹:該用哪一個?

START
│
├─ 需要修改資料 (INSERT/UPDATE/DELETE/DDL)? → YES → 【預存程序】
│
├─ 需要 OUTPUT 參數或多結果集? → YES → 【預存程序】
│
├─ 需要交易控制 (BEGIN/COMMIT/ROLLBACK)? → YES → 【預存程序】
│
├─ 需要動態 SQL / 暫存表 / TRY-CATCH? → YES → 【預存程序】
│
├─ 需在 SELECT/WHERE/JOIN/APPLY 中直接使用? → YES
│   ├─ 回傳單一值,且邏輯極簡單 (無資料存取)? → YES → 【標量函數】(小心 RBAR)
│   ├─ 回傳結果集,邏輯為單一 SELECT? → YES → 【內聯表值函數 iTVF】⭐ 最佳
│   └─ 回傳結果集,需多步驟邏輯/中繼表? → YES → 【多語句表值函數 mTVF】⚠️ 評估改用預存程序+INSERT INTO #temp
│
└─ 純粹封裝業務邏輯、批次處理、維護作業? → 【預存程序】

實戰範例:同樣邏輯的三種寫法比較

需求:依客戶 ID 取得其訂單總金額與筆數

❌ 標量函數 (RBAR 災難)

CREATE FUNCTION dbo.sf_CustOrderSummary (@CustID INT)
RETURNS DECIMAL(18,2)
AS BEGIN
    DECLARE @Total DECIMAL(18,2);
    SELECT @Total = SUM(Amount) FROM dbo.Orders WHERE CustomerID = @CustID;
    RETURN @Total;
END;
-- 呼叫:SELECT CustomerID, dbo.sf_CustOrderSummary(CustomerID) FROM dbo.Customers; -- 每列執行一次!

⚠️ 多語句表值函數 (估計列數固定 100)

CREATE FUNCTION dbo.mtvf_CustOrderSummary (@CustID INT)
RETURNS @Result TABLE (TotalAmount DECIMAL(18,2), OrderCount INT)
AS BEGIN
    INSERT @Result
    SELECT SUM(Amount), COUNT(*) FROM dbo.Orders WHERE CustomerID = @CustID;
    RETURN;
END;
-- 呼叫:SELECT * FROM dbo.mtvf_CustOrderSummary(1);

✅ 內聯表值函數 (最佳,可內展、並行、用索引)

CREATE FUNCTION dbo.itvf_CustOrderSummary (@CustID INT)
RETURNS TABLE
AS RETURN
    SELECT SUM(Amount) AS TotalAmount, COUNT(*) AS OrderCount
    FROM dbo.Orders
    WHERE CustomerID = @CustID;
-- 呼叫:SELECT c.*, o.* FROM dbo.Customers c CROSS APPLY dbo.itvf_CustOrderSummary(c.CustomerID) o;

✅ 預存程序 (需輸出參數/多結果集/交易時)

CREATE PROCEDURE usp_CustOrderSummary
    @CustID INT,
    @TotalAmount DECIMAL(18,2) OUTPUT,
    @OrderCount INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT @TotalAmount = SUM(Amount), @OrderCount = COUNT(*)
    FROM dbo.Orders WHERE CustomerID = @CustID;
    
    -- 可再回傳明細結果集
    SELECT OrderID, OrderDate, Amount FROM dbo.Orders WHERE CustomerID = @CustID;
END;
-- 呼叫:DECLARE @Tot DECIMAL(18,2), @Cnt INT; EXEC usp_CustOrderSummary 1, @Tot OUT, @Cnt OUT;

安全性與部署建議

  • 模組簽署:為預存程序/函數簽署憑證,授予精確權限 (如 VIEW SERVER STATE) 而不給呼叫者高權限登入。
  • 避免 dbo 擁有者鏈破裂:跨資料庫/伺服器物件參考時,確保擁有者一致或使用憑證。
  • 版本控管:所有函數/程序腳本納入 Git,CI/CD 自動部署,禁止手動右鍵修改。

小結

**口訣:查詢內嵌用 iTVF,業務流程用 Procedure,標量函數能不用就不用。** 理解 RBAR、內展、估計列數三大效能殺手,並善用 CROSS/OUTER APPLY 搭配 iTVF,即能寫出高效能、可維護的 T-SQL 程式碼。

copyright © 萬盛學電腦網 all rights reserved