前言

SQL Server CLR 整合 (SQL CLR) 允許在資料庫引擎內部執行 .NET 組件 (C#/VB.NET)，將複雜運算、正則表達式、加密、外部 API 呼叫等邏輯下推至資料庫層，避免資料往返應用層。本文以 **URL Decode** 為例，完整演示從 C# 類庫編譯、註冊組件、建立 T-SQL 包裝函數到實際呼叫的全流程。

一、前置條件與安全模型

權限集	權限範圍	適用場景	風險
SAFE (預設)	純運算、記憶體、無外部存取	字串處理、數學、邏輯運算	最低
EXTERNAL_ACCESS	讀寫檔案、網路、登錄、環境變數	檔案處理、呼叫 Web Service	中
UNSAFE	非受控程式碼、P/Invoke、靜態變數、執行緒控制	極少數極高效能/互操作場景	**高**，可能導致引擎不穩定

原則：能用 SAFE 就不用 EXTERNAL_ACCESS；能用 EXTERNAL_ACCESS 就不用 UNSAFE。本文範例需引用 System.Web (非純 managed)，故示範 UNSAFE，生產環境請改用純 .NET 實作 (如 System.Net.WebUtility.UrlDecode) 以達 SAFE。

二、C# 類庫專案建立

1. 專案設定

• 目標框架：.NET Framework 4.8 (SQL Server 2019/2022 支援) / .NET 6.0+ (需 SQL Server 2022+ 並啟用 clr strict security 相容設定)。
• 簽署強名稱金鑰 (強制)：專案屬性 → Signing → 勾選「Sign the assembly」→ New/選擇 .snk。
• 類別標記 [SqlFunction] / [SqlProcedure]。

2. 程式碼 (SqlClrUtils.cs)

using System;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Web;  // 需參考 System.Web.dll

public partial class SqlClrUtils
{
    /// 
URL 解碼，支援 NULL 輸入
    [SqlFunction(IsDeterministic = true, IsPrecise = true, DataAccess = DataAccessKind.None)]
    public static SqlString UrlDecode(SqlString input)
    {
        if (input.IsNull) return SqlString.Null;
        // System.Web.HttpUtility 需 UNSAFE，生產建議改用 System.Net.WebUtility (SAFE)
        return new SqlString(HttpUtility.UrlDecode(input.Value));
    }

    /// 
正則表達式替換 (展示 REGEX 功能)
    [SqlFunction(IsDeterministic = true)]
    public static SqlString RegexReplace(SqlString input, SqlString pattern, SqlString replacement)
    {
        if (input.IsNull) return SqlString.Null;
        return new SqlString(System.Text.RegularExpressions.Regex.Replace(input.Value, pattern.Value, replacement.Value));
    }
}

3. 編譯產出 SqlClrUtils.dll 複製至伺服器路徑 (如 C:\CLR\SqlClrUtils.dll)

三、T-SQL 部署腳本

-- 1. 啟用 CLR 整合 (執行個體級，需 sysadmin)
EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_configure 'clr enabled', 1; RECONFIGURE;
GO

-- 2. 設定資料庫 TRUSTWORTHY (允許 EXTERNAL_ACCESS/UNSAFE 組件)
-- 生產環境建議改用非對稱金鑰/憑證簽署登入，避免 TRUSTWORTHY 風險
ALTER DATABASE [YourDB] SET TRUSTWORTHY ON;
GO

-- 3. 註冊相依組件 System.Web (若用 HttpUtility)
CREATE ASSEMBLY [System.Web]
FROM 'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\System.Web.dll'
WITH PERMISSION_SET = UNSAFE;  -- System.Web 含非受控碼
GO

-- 4. 註冊自訂組件
CREATE ASSEMBLY [SqlClrUtils]
FROM 'C:\CLR\SqlClrUtils.dll'
WITH PERMISSION_SET = UNSAFE;
GO

-- 5. 建立 T-SQL 包裝函數
CREATE FUNCTION [dbo].[SqlUrlDecode] (@urlstr NVARCHAR(4000))
RETURNS NVARCHAR(4000)
AS EXTERNAL NAME [SqlClrUtils].[SqlClrUtils].[UrlDecode];
GO

CREATE FUNCTION [dbo].[SqlRegexReplace] (@input NVARCHAR(MAX), @pattern NVARCHAR(4000), @replacement NVARCHAR(4000))
RETURNS NVARCHAR(MAX)
AS EXTERNAL NAME [SqlClrUtils].[SqlClrUtils].[RegexReplace];
GO

四、呼叫驗證

-- URL Decode
SELECT dbo.SqlUrlDecode(N'https%3A%2F%2Fexample.com%2Fpath%3Fq%3D%E4%B8%AD%E6%96%87');
-- 結果: https://example.com/path?q=中文

-- Regex Replace
SELECT dbo.SqlRegexReplace(N'Order-123-Item-456', N'\d+', N'[NUM]');
-- 結果: Order-[NUM]-Item-[NUM]

五、生產環境強化清單

1. 移除 TRUSTWORTHY：建立非對稱金鑰 → 對應登入 → 授權 EXTERNAL ACCESS ASSEMBLY / UNSAFE ASSEMBLY。
2. 降級權限集：改用 System.Net.WebUtility (SAFE) 替代 System.Web.HttpUtility。
3. 版本管理：組件版本號語義化，升級用 ALTER ASSEMBLY ... WITH UNCHECKED DATA (需小心)。
4. 效能基準：CLR 呼叫有 **Context Switch 開銷** (Managed ↔ Unmanaged)，單次 ~1-5μs。**批次處理 (Table-Valued Parameter / SqlPipe 送資料流) 遠優於逐行標量函數**。
5. 監控：sys.dm_clr_appdomains, sys.dm_clr_loaded_assemblies, sys.dm_exec_clr_appdomain_stats 監控 AppDomain 記憶體、執行緒、異常。
6. 除錯：Visual Studio → Debug → Attach to Process → sqlservr.exe → 斷點在 C# 代碼 (需 PDB 檔部署)。

六、適用性評估：何時選 CLR？

場景	推薦方案	理由
複雜字串解析/正則/編碼	CLR 函數	T-SQL 正則弱、字串操作慢；CLR 單次編譯、JIT 優化
加密/雜湊/簽章	CLR 函數	.NET BCL 算法完整、常數時間實現
大量集合運算 (排序/分組/統計)	T-SQL / Columnstore	集合運算 SQL 原生最強，CLR 需自行實作並行
外部 API / 檔案系統	應用層 / Service Broker / 外部啟動器	資料庫不應直接發起外部網路呼叫，耦合風險高
遞迴/圖演算法	CLR 預存程序 / TVF	T-SQL 遞迴 CTE 深度/效能受限

結語

SQL CLR 是**將 .NET 生態能力引入資料庫引擎**的強大橋樑，但伴隨安全、穩定、維護成本。遵循「SAFE 優先、最小權限、批次介面、嚴格監控」原則，僅在 T-SQL 確實難以高效表達的**純計算密集型邏輯** (正則、編碼、加密、複雜演算) 中引入，才能收穫效能紅利而不引入系統風險。