開發人員常在「該寫函數還是預存程序?」猶豫。本文從回傳型別、副作用限制、呼叫方式、效能特性、安全性五大維度深度對比,並給出明確的選型決策樹。
| 維度 | 自訂函數 (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 完整支援中斷點、變數監看、呼叫堆疊。 |
RETURNS TABLE AS RETURN SELECT ... 無 BEGIN...END。優化器**內展**為查詢片段,**零額外開銷**,可並行、可用索引。✅ **首選**。RETURNS @t TABLE ... BEGIN ... INSERT @t ... RETURN。優化器視為黑盒,**估計固定 100 列**,常導致嚴重低估/高估,無法並行。⚠️ 慎用。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
│
└─ 純粹封裝業務邏輯、批次處理、維護作業? → 【預存程序】
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; -- 每列執行一次!
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) 而不給呼叫者高權限登入。**口訣:查詢內嵌用 iTVF,業務流程用 Procedure,標量函數能不用就不用。** 理解 RBAR、內展、估計列數三大效能殺手,並善用 CROSS/OUTER APPLY 搭配 iTVF,即能寫出高效能、可維護的 T-SQL 程式碼。