SQL Server 2000 使用者定義函數：替代游標實現高效字串轉換

使用者定義函數（UDF）：邏輯封裝與效能優化的雙重利器

核心價值：替代游標，大幅降低系統開銷

傳統處理逐行邏輯（如漢字轉拼音）需使用游標：開啟游標 → 逐行 FETCH → 查表轉換 → 更新回寫。游標缺點：記憶體佔用大、鎖定資源、大數據集處理極慢。

UDF 允許將邏輯封裝為**可在 SQL 陳述式中直接呼叫的函數**，引擎內部優化執行，避免游標開銷。

實戰對比：漢字轉拼音案例

方案一：使用 UDF（推薦）

CREATE FUNCTION Translate(@Original_Text VARCHAR(100))
RETURNS VARCHAR(500)
AS
BEGIN
    DECLARE @intLength TINYINT, @strTemp VARCHAR(10)
    DECLARE @strResult VARCHAR(500), @strChar VARCHAR(2)
    DECLARE @i TINYINT
    
    SET @intLength = LEN(@Original_Text)
    SET @strResult = ''
    SET @i = 1
    
    WHILE @i <= @intLength
    BEGIN
        SELECT @strChar = SUBSTRING(@Original_Text, @i, 1)
        SET @strTemp = NULL
        SELECT @strTemp = spell FROM wordspell WHERE Word = @strChar
        SET @strResult = @strResult + ISNULL(@strTemp, @strChar)
        SET @i = @i + 1
    END
    RETURN @strResult
END
GO

-- 單一陳述式完成批次更新
UPDATE phonecode 
SET namesame = Translate(name), 
    addsame = Translate(address)

方案二：使用游標（不推薦）

CREATE PROC trans AS
DECLARE @i INT, @char VARCHAR(2), @tempchr VARCHAR(2)
DECLARE @strtemp VARCHAR(100), @strtemp1 VARCHAR(100)
DECLARE @strname VARCHAR(100), @straddr VARCHAR(100)

DECLARE Cur_trans CURSOR LOCAL DYNAMIC FOR 
SELECT [name], [address] FROM phonecode

OPEN Cur_trans
FETCH Cur_trans INTO @strname, @straddr
SET NOCOUNT ON

WHILE @@FETCH_STATUS = 0
BEGIN
    -- 處理姓名
    SET @i = 1
    SET @strtemp = ''
    SET @strname = LTRIM(RTRIM(@strname))
    WHILE @i <= LEN(@strname)
    BEGIN
        SELECT @char = SUBSTRING(@strname, @i, 1)
        SET @tempchr = NULL
        SELECT @tempchr = spell FROM TYPER.wordspell WHERE Word = @char
        SET @strtemp = @strtemp + ISNULL(@tempchr, @char)
        SET @i = @i + 1
    END
    
    -- 處理地址（邏輯重複）
    SET @i = 1
    SET @strtemp1 = ''
    SET @char = ''
    SET @straddr = LTRIM(RTRIM(@straddr))
    WHILE @i <= LEN(@straddr)
    BEGIN
        SELECT @char = SUBSTRING(@straddr, @i, 1)
        SET @tempchr = NULL
        SELECT @tempchr = spell FROM TYPER.wordspell WHERE Word = @char
        SET @strtemp1 = @strtemp1 + ISNULL(@tempchr, @char)
        SET @i = @i + 1
    END
    
    UPDATE phonecode 
    SET namesame = @strtemp, addsame = @strtemp1 
    WHERE CURRENT OF Cur_trans
    
    FETCH NEXT FROM Cur_trans INTO @strname, @straddr
END

CLOSE Cur_trans
DEALLOCATE Cur_trans

UDF 類型與適用場景

UDF 效能優化關鍵點

1. 避免標量 UDF 在大量資料列上逐行執行：考慮改寫為內嵌資料表值函數或直接在查詢中展開邏輯
2. SCHEMABINDING：建立函數時加上 WITH SCHEMABINDING，允許優化器更多最佳化機會
3. 確定性標記：相同輸入必得相同輸出的函數標記為確定性，可用於計算欄位、索引檢視表
4. 減少資料存取：函數內避免複雜查詢，必要時使用暫存表/表變數暫存中間結果

版本演進提示

• SQL Server 2000：引入 UDF，僅支援 T-SQL 實作
• SQL Server 2005+：支援 CLR 整合（C#/VB.NET 實作 UDF），計算密集型任務效能大幅提升
• SQL Server 2019+：標量 UDF 內聯優化，自動將簡單標量 UDF 展開為查詢邏輯，解決逐行執行效能問題

最佳實務建議

• 優先使用**內嵌資料表值函數**（單一 SELECT），效能最接近直接查詢
• 標量 UDF 僅用於簡單計算/格式化，避免在 WHERE/JOIN/SELECT 清單中對大量資料列呼叫
• 游標僅作為最後手段：無法用集合操作解決、需逐行呼叫外部資源、動態 SQL 建構等場景
• 所有 UDF 加上 WITH SCHEMABINDING，除非確有結構變更需求