SQL Server CLR 极速入门：10 分钟掌握啟用、開發、部署與進階實戰

SQL Server CLR 整合 .NET Framework，讓您用 C#/VB.NET 編寫預存程序、函數、觸發器、使用者定義型別 (UDT) 與彙總函數。部署後隨資料庫備份還原遷移，效能高、安全、開發效率極佳。本文從零開始，10 分鐘帶您完成首個 CLR 函數，並分享「全文搜尋效能優化」與「正則表達式」兩大進階實戰案例。

一、啟用 CLR (一次性設定)

EXEC sp_configure 'clr enabled', 1;
RECONFIGURE;
GO

二、Visual Studio 開發與部署標準流程

1. VS → 新增專案 → 資料庫 → SQL Server 專案 (目標 .NET Framework 2.0/3.5)。
2. 加入資料庫參考：指定部署目標資料庫連線。
3. 右鍵專案 → 加入 → 新增項目 → 使用者定義函數 (命名 Function1.cs)。
4. 編寫程式碼 (範例回傳 "Hello")：

   using System;
   using System.Data.SqlTypes;
   using Microsoft.SqlServer.Server;
   
   public partial class UserDefinedFunctions
   {
       [Microsoft.SqlServer.Server.SqlFunction]
       public static SqlString Function1()
       {
           return new SqlString("Hello");
       }
   }

5. 右鍵專案 → 部署，輸出視窗顯示「部署成功」。

三、SSMS 驗證執行

SELECT dbo.Function1();  -- 回傳 "Hello"
-- 可在 可程式性 → 函數 → 純量值函數 看到 dbo.Function1

四、進階實戰 1：大欄位 LIKE 查詢效能突破 (ContainsOne)

場景：OA 系統 Articles 表 8000 筆資料，txtContent 為 varchar(max)，3000 筆超過 4000 字元。LIKE '%關鍵字%' 耗時 10-12 秒，資料庫引擎優化顧問建議 0%。

根因：LIKE '%...%' 無法使用索引，且長字串掃描成本隨長度線性增長。

CLR 解法：利用 .NET String.Contains (Boyer-Moore/Horspool 演算法) 極速子字串搜尋。

[Microsoft.SqlServer.Server.SqlFunction]
public static SqlBoolean ContainsOne(SqlChars input, string search)
{
    return new string(input.Value).Contains(search);
}

測試結果：

• 8000 筆：LIKE 10-12 秒 → ContainsOne 1 秒。
• 擴充至 6 萬筆 (模擬 8 年增長)：LIKE 82 秒 → ContainsOne 5 秒。

查詢寫法：SELECT COUNT(*) FROM dbo.Articles WHERE dbo.ContainsOne(txtContent, '關鍵字') = 1

五、進階實戰 2：正則表達式替換與搜尋 (RegexReplace / RegexSearch)

[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString RegexReplace(SqlChars input, SqlString pattern, SqlString replacement)
{
    return Regex.Replace(new string(input.Value), pattern.Value, replacement.Value, RegexOptions.Compiled);
}

[Microsoft.SqlServer.Server.SqlFunction]
public static SqlBoolean RegexSearch(SqlChars input, string pattern)
{
    return Regex.Match(new string(input.Value), pattern, RegexOptions.Compiled).Success;
}

用法：SELECT dbo.RegexReplace('內容', '<.+?>', '') → 移除 HTML 標籤。

六、完整工具類程式碼 (含 ContainsAny / ContainsAll)

using System;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text.RegularExpressions;

public partial class UserDefinedFunctions
{
    // 1. 單一關鍵字包含
    [SqlFunction]
    public static SqlBoolean ContainsOne(SqlChars input, string search)
    {
        return new string(input.Value).Contains(search);
    }

    // 2. 任一關鍵字包含 (逗號分隔)
    [SqlFunction]
    public static SqlBoolean ContainsAny(SqlChars input, string search)
    {
        var str = new string(input.Value);
        foreach (var item in search.Split(','))
            if (str.Contains(item)) return true;
        return false;
    }

    // 3. 所有關鍵字皆包含
    [SqlFunction]
    public static SqlBoolean ContainsAll(SqlChars input, string search)
    {
        var str = new string(input.Value);
        foreach (var item in search.Split(','))
            if (!str.Contains(item)) return false;
        return true;
    }

    // 4. 正則替換
    [SqlFunction]
    public static SqlString RegexReplace(SqlChars input, SqlString pattern, SqlString replacement)
    {
        return Regex.Replace(new string(input.Value), pattern.Value, replacement.Value, RegexOptions.Compiled);
    }

    // 5. 正則搜尋
    [SqlFunction]
    public static SqlBoolean RegexSearch(SqlChars input, string pattern)
    {
        return Regex.Match(new string(input.Value), pattern, RegexOptions.Compiled).Success;
    }
}

七、關鍵注意事項

1. 部署方式：官方支援 CREATE ASSEMBLY 手動部署，但 強烈建議直接用 Visual Studio 一鍵部署，自動處理組件註冊、函數建立、符號檔上傳。
2. 隨資料庫走：CLR 組件部署在資料庫內，備份/還原/AlwaysOn 同步時自動隨行，無額外部署動作。
3. 型別對應：SqlString → nvarchar(4000)；SqlChars → nvarchar(max)。大欄位優先用 SqlChars；小欄位用 SqlString 效能更好。
4. 布林回傳：CLR bool 對應 SQL bit (1/0/NULL)，不可直接當布林用，需寫 dbo.Func(...) = 1。