您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SQL Server 使用者定義函數深度解析：三大類型、建立語法與效能實戰

編輯：關於SqlServer

使用者定義函數 (UDF) 是 SQL Server 2000 引入的重要可程式化物件，讓開發者將商務邏輯封裝為可重用的運算單元。UDF 可像系統函數一樣嵌入 SELECT、WHERE、CHECK、DEFAULT 等運算式，也能透過 EXECUTE 呼叫。依傳回值形式分為純量函數、內嵌資料表值函數、多陳述式資料表值函數 三類，各具適用場景與效能特徵。

三大類型架構對比

類型	傳回值	函數主體	相當於	典型用途
純量函數	單一標量值（除 text/ntext/image/cursor/timestamp/table 外所有型別）	`BEGIN ... END` 區塊，含多條 T-SQL 陳述式，需 `RETURN` 回傳	延伸內建函數、複雜計算封裝	格式化字串、稅率計算、自訂日期運算
內嵌資料表值函數	單一資料表結果集	無 `BEGIN/END`，直接 `RETURN (SELECT ...)`	參數化檢視表	依參數動態篩選資料、替代帶參數檢視表
多陳述式資料表值函數	資料表變數	`BEGIN ... END` 區塊，宣告 `@ret TABLE(...)`，多次 `INSERT` 後 `RETURN`	複雜 ETL 轉換、多步驟篩選合併	分階段資料清洗、彙總多來源、遞迴階層查詢

建立語法範本

1. 純量函數

CREATE FUNCTION dbo.fn_CalcTax (@Amount money, @Rate float)
RETURNS money
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @Tax money;
    SET @Tax = ROUND(@Amount * @Rate, 2);
    RETURN @Tax;
END

2. 內嵌資料表值函數

CREATE FUNCTION dbo.fn_GetOrdersByYear (@Year int)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN (
    SELECT OrderID, CustomerID, OrderDate, TotalAmount
    FROM dbo.Orders
    WHERE YEAR(OrderDate) = @Year
);

3. 多陳述式資料表值函數

CREATE FUNCTION dbo.fn_TopCustomersByRegion (@Region nvarchar(30), @TopN int)
RETURNS @Result TABLE (
    CustomerID nchar(5),
    CompanyName nvarchar(40),
    TotalOrders int,
    TotalAmount money
)
WITH SCHEMABINDING
AS
BEGIN
    INSERT @Result
    SELECT c.CustomerID, c.CompanyName,
           COUNT(o.OrderID), SUM(od.UnitPrice * od.Quantity * (1 - od.Discount))
    FROM dbo.Customers c
    JOIN dbo.Orders o ON c.CustomerID = o.CustomerID
    JOIN dbo.[Order Details] od ON o.OrderID = od.OrderID
    WHERE c.Region = @Region
    GROUP BY c.CustomerID, c.CompanyName
    ORDER BY TotalAmount DESC;

    -- 只保留前 N 筆
    DELETE FROM @Result
    WHERE CustomerID NOT IN (
        SELECT TOP (@TopN) CustomerID FROM @Result ORDER BY TotalAmount DESC
    );
    RETURN;
END

關鍵選項與限制

SCHEMABINDING：綁定參考物件結構，防止誤刪/修改導致函數失效；強烈建議永遠加上。
ENCRYPTION：加密定義文字，隱藏邏輯（但可透過 DAC 連線解密）。
副作用限制：UDF 不可變更資料庫狀態（不得 INSERT/UPDATE/DELETE 永久資料表、不得動態 SQL、不得呼叫非確定性擴充預存程序）。
確定性：相同輸入必得相同輸出始為「確定性」；僅確定性函數可用於計算欄、索引檢視表、CHECK 約束。
巢狀呼叫：最多 32 層；純量函數在 SELECT 清單中逐列執行，大量資料易成效能瓶頸。

效能最佳實務

優先用內嵌資料表值函數：查詢最佳化器可展開定義並產生最佳執行計畫，效能接近直接寫 JOIN/WHERE。
純量函數避免用於大量列運算：改用 CROSS APPLY 內嵌函數、或將邏輯內嵌查詢、或改為預存程序輸出結果集。
多陳述式函數謹慎使用：資料表變數無統計資料，最佳化器假設 1 列，易導致嚴重誤估；考慮改用暫存表 + 預存程序。
加上 SCHEMABINDING 可讓最佳化器啟用更多優化（如略過 Halloween Protection）。
避免在 UDF 內存取跨資料庫/連結伺服器物件，序列化與網路延遲會放大逐列執行代價。

常見應用模式

計算欄：ALTER TABLE t ADD Total AS dbo.fn_CalcLineTotal(Qty, Price, Discount)（需確定性+SCHEMABINDING）。
CHECK 約束：CONSTRAINT chk_ValidDate CHECK (dbo.fn_IsBusinessDay(DateCol) = 1)。
參數化資料存取：SELECT * FROM dbo.fn_GetOrdersByYear(2023) 替代動態 SQL。
模組化商務規則：將定價、折扣、稅制邏輯集中於純量函數，前端/報表/ETL 共享。

善用三類 UDF 的特性差異，配合 SCHEMABINDING、CROSS APPLY、確定性設計，即能在保持程式碼可維護性的同時，獲得接近內建函數的執行效能。