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

SQL Server 使用者定義函數 (UDF) 深度解析與最佳實踐

編輯：關於SqlServer

UDF 類型總覽

類型	回傳值	函數主體	典型用途
純量函數	單一標量值 (int, varchar, money, datetime...)	T-SQL 陳述式區塊，可含控制流程	封裝商業規則計算、格式化、查詢清單/WHERE 條件中直接呼叫
內聯資料表值函數	TABLE (單一 SELECT 衍生)	單一 `RETURN (SELECT ...)`	參數化視圖、JOIN 來源、最佳化器可展開查詢計畫
多陳述式資料表值函數	TABLE (明確定義結構)	完整 T-SQL 批次 (變數、迴圈、游標、DML...)	複雜邏輯產生結果集、需中繼處理、動態 SQL

純量函數：從重複程式碼到可維護邏輯

範例：判斷產品是否需補貨

CREATE FUNCTION dbo.fnNeedToReorder (
    @ReorderLevel  smallint,
    @UnitsInStock  smallint,
    @UnitsOnOrder  smallint,
    @Discontinued  bit
) RETURNS bit AS
BEGIN
    IF @Discontinued = 1 RETURN 0;                 -- 停產不補貨
    IF @UnitsInStock + @UnitsOnOrder < @ReorderLevel RETURN 1;
    RETURN 0;
END

呼叫方式：

SELECT ProductID, dbo.fnNeedToReorder(ReorderLevel, UnitsInStock, UnitsOnOrder, Discontinued) AS NeedReorder
FROM Products;

若業務規則變更 (如新增供應商交期判斷)，只需修改函數主體，所有呼叫點自動生效。

進階：巢狀函數與決定性

CREATE FUNCTION dbo.fnCOM_2Digits(@sValue varchar(2)) RETURNS varchar(2) AS
BEGIN
    IF LEN(@sValue) < 2 SET @sValue = '0' + @sValue;
    RETURN @sValue;
END
GO

CREATE FUNCTION dbo.fnCOM_StandardDate(@dtDate datetime) RETURNS varchar(10) AS
BEGIN
    RETURN dbo.fnCOM_2Digits(CAST(MONTH(@dtDate) AS varchar(2))) + '/' +
           dbo.fnCOM_2Digits(CAST(DAY(@dtDate) AS varchar(2))) + '/' +
           CAST(YEAR(@dtDate) AS varchar(4));
END

限制：非決定性內建函數 (GETDATE(), NEWID(), RAND()) 不可在 UDF 內直接呼叫，否則報錯。需在外層取值後傳入參數。

資料表值函數：參數化視圖的最佳替代

內聯式：最佳化器可「內展開」

CREATE FUNCTION dbo.fnGetEmployeesByCity(@City varchar(30))
RETURNS TABLE AS
RETURN (
    SELECT FirstName, LastName, Address
    FROM Employees
    WHERE City = @City
);

使用方式如同檢視表：

SELECT * FROM dbo.fnGetEmployeesByCity('Seattle');
SELECT e.*, o.OrderDate
FROM dbo.fnGetEmployeesByCity('London') e
JOIN Orders o ON e.EmployeeID = o.EmployeeID;

優勢：查詢最佳化器將函數定義合併至外部查詢計畫，享有索引求值、聯結重排序等完整最佳化。

多陳述式：複雜邏輯與中繼處理

CREATE FUNCTION dbo.fnGetEmployeesByCity_Multi(@City varchar(30))
RETURNS @Result TABLE (FirstName varchar(20), LastName varchar(30), Address varchar(60), Note varchar(100))
AS
BEGIN
    INSERT @Result
    SELECT FirstName, LastName, Address, ''
    FROM Employees WHERE City = @City;

    IF NOT EXISTS (SELECT 1 FROM @Result)
        INSERT @Result VALUES ('', '', '', '指定城市無員工資料');

    RETURN;
END

效能陷阱與避坑指南

情境	風險	對策
純量 UDF 用於 SELECT 清單，資料列數百萬	逐列執行函數，CPU 暴增、平行度降級	改用內聯運算式、計算欄位、或內聯 TVF
多陳述式 TVF 大量資料	最佳化器視為黑盒，估計固定 100 列，導致計畫錯誤	加入 `OPTION (RECOMPILE)`、或改寫為預存程序/暫存表
UDF 內查詢基礎表 (如 fnNeedToReorder 內 SELECT Products)	每列觸發獨立查詢，I/O 放大	將所需欄位以參數傳入，避免 UDF 內存取資料表
遞迴深度超過 32 層	執行錯誤	改用迴圈、CTE 或應用層處理