SQL Server 2005 CLR 整合開發指南：從架構原理到 Visual Studio 部署除錯的完整實戰

前言

SQL Server 2005 引入 CLR 整合 (CLR Integration)，在資料庫引擎內託管 .NET Framework 2.0 CLR，允許以 C#、VB.NET、C++ 撰寫預存程序、觸發器、使用者定義函數 (UDF)、使用者定義型別 (UDT)、使用者定義彙總 (UDA)。這打破 T-SQL 在複雜運算、正則表達式、加密、檔案操作、Web Service 呼叫等領域的表達力限制，實現資料層邏輯與應用層共用程式碼庫。

CLR 整合架構與啟用

-- 啟用 CLR (預設關閉)
EXEC sp_configure 'clr enabled', 1; RECONFIGURE;

安全權限集 (Permission Set)：

部署單位：組件。簽署強名稱或憑證，建立非對稱金鑰/憑證登入，授予對應權限集。

開發流程：Visual Studio 2005/2008/2010+ (SSDT) 整合體驗

1. 建立 SQL Server 專案：專案類型「SQL Server Database Project」或舊版「SQL Server Project」。
2. 加入項目：預存程序、函數、觸發器、UDT、UDA — VS 提供程式碼模板，自動產生屬性 [SqlProcedure]、[SqlFunction]、[SqlTrigger] 等。
3. 撰寫程式碼：

   [SqlFunction(DataAccess = DataAccessKind.Read)]
   public static SqlInt32 CountOrdersByCustomer(SqlString customerId)
   {
       using (SqlConnection conn = new SqlConnection("context connection=true"))
       {
           conn.Open();
           SqlCommand cmd = new SqlCommand(
               "SELECT COUNT(*) FROM Orders WHERE CustomerID = @cid", conn);
           cmd.Parameters.AddWithValue("@cid", customerId);
           return (int)cmd.ExecuteScalar();
       }
   }

4. 建置：產生 .dll 組件、.pdb 偵錯符號。
5. 部署：VS 自動執行 CREATE ASSEMBLY、CREATE PROCEDURE/FUNCTION...，上傳組件二進位、原始碼、PDB 至資料庫。
6. 偵錯：F5 直接附加至 sqlservr.exe 處理序，支援跨語言無縫偵錯 (T-SQL → CLR → T-SQL)，斷點、變數監看、呼叫堆疊完整運作。

CLR vs T-SQL vs 擴充預存程序 (XP) 深度比較

最佳實踐與避坑指南

1. 只在 T-SQL 不擅長的領域用 CLR：複雜數學、正則表達式、加密雜湊、XML/JSON 深度處理、檔案/網路存取。集合式資料操作仍首選 T-SQL。
2. 避免逐列處理：CLR 函數若在查詢中對大量列調用，會產生嚴重效能問題。改用 SqlDataReader 批次讀取或改寫為 T-SQL 集合邏輯。
3. 靜態變數 = 共享狀態風險：SQL Server 進程內多連線共享 AppDomain，靜態欄位會跨 Session 洩漏資料/造成競爭。嚴禁在 SAFE/EXTERNAL_ACCESS 組件使用靜態可變狀態。
4. 正確管理 Context Connection：context connection=true 重用現有連線，不可開啟交易、不可多重作用中巢狀 (MARS 除外)。外部連線用一般連線字串。
5. 組件版本升級：ALTER ASSEMBLY ... WITH VISIBILITY ON/OFF，搭配強名稱簽署，避免 DLL Hell。
6. 監控 CLR 健康度：sys.dm_clr_appdomains、sys.dm_clr_loaded_assemblies、sys.dm_clr_tasks、sys.dm_os_memory_clerks (type = 'CLR')。

實戰範例：使用者定義彙總 — 中位數

[Serializable]
[SqlUserDefinedAggregate(Format.UserDefined, IsInvariantToNulls = true,
    IsInvariantToDuplicates = false, IsInvariantToOrder = false, MaxByteSize = -1)]
public struct Median : IBinarySerialize
{
    private List _values;
    public void Init() { _values = new List(); }
    public void Accumulate(SqlDouble value) { if (!value.IsNull) _values.Add(value.Value); }
    public void Merge(Median other) { _values.AddRange(other._values); }
    public SqlDouble Terminate()
    {
        if (_values.Count == 0) return SqlDouble.Null;
        _values.Sort();
        int mid = _values.Count / 2;
        return _values.Count % 2 == 0
            ? (_values[mid - 1] + _values[mid]) / 2.0
            : _values[mid];
    }
    public void Read(System.IO.BinaryReader r) { /* 反序列化 */ }
    public void Write(System.IO.BinaryWriter w) { /* 序列化 */ }
}

使用：SELECT dbo.Median(Score) FROM Exams GROUP BY Class;

結語

CLR 整合將 .NET 生態系統的強大威力帶入資料庫核心，但權力越大、責任越大。遵循「T-SQL 優先、CLR 補位、權限最小、偵錯優先、監控常態」原則，即能在保障穩定安全的前提下，釋放資料層運算的無限潛能。