SQL Server 2005 (Yukon) CLR 整合架構深度解析

CLR 整合：資料庫引擎與 .NET 執行環境的深度融合

SQL Server 2005 (代號 Yukon) 首度將 .NET Framework Common Language Runtime (CLR) 宿主於資料庫引擎內部，開啟資料庫程式設計新範式。

核心優勢

• 語言豐富性：支援 C#, VB.NET 等 OO 語言，解決 T-SQL 在複雜運算、字串處理、正則表達式、加密、檔案/登錄存取上的短板。
• 效能提升：.NET 程式碼 JIT 編譯為原生碼，重複執行效率遠高於解譯型 T-SQL；適合 CPU 密集型邏輯 (科學計算、圖像處理、複雜解析)。
• 程式碼重用：直接引用 .NET Framework Class Library (FCL) 及自訂組件，避免邏輯重寫。
• 型別安全與安全性：CLR 強制型別檢查、程式碼存取安全 (CAS)、記憶體管理 (GC)，降低緩衝區溢位、指標錯誤風險。

新型資料庫物件

• Managed Stored Procedures (托管預存程序)
• Managed User-Defined Functions (UDF, 含標量/資料表值)
• Managed Triggers (托管觸發器)
• User-Defined Types (UDT, 自訂複雜型別)
• User-Defined Aggregates (UDA, 自訂彙總函數)

架構挑戰與解決：Hosting (寄宿) 機制

SQL Server 為高併發、高可靠服務，擁有自主記憶體管理 (Buffer Pool)、排程器 (SOS Scheduler, Fiber 模式)、同步原語。CLR 預設管理機制與之衝突，需深度整合：

安全權限模型 (三層)

1. SAFE (預設)：純運算、資料存取，無外部存取 (檔案、網路、登錄、COM)。等同傳統 T-SQL 權限。
2. EXTERNAL_ACCESS：允許讀寫檔案、登錄、網路連線、存取環境變數。需資料庫設定 TRUSTWORTHY ON 或簽署非對稱金鑰登入。
3. UNSAFE：完全信任，可呼叫 Unmanaged Code、操作記憶體指標、建立執行緒。極度不建議生產使用，僅限除錯/實驗。

適用場景判斷

• ✅ 適合：複雜數學/統計運算、字串解析/正則、加密雜湊、檔案/登錄存取 (EXTERNAL_ACCESS)、替代擴展預存程序 (XP)。
• ❌ 不適合：簡單 CRUD、集合操作 (T-SQL 優化更好)、大量資料搬移 (SSIS/BCP 更佳)、高併發 OLTP 關鍵路徑 (Context Switch 開銷)。

部署流程

-- 1. 啟用 CLR (伺服器層級)
EXEC sp_configure 'clr enabled', 1; RECONFIGURE;

-- 2. 建立組件 (需 VARBINARY 或 FROM 路徑)
CREATE ASSEMBLY MyUtils FROM 'C:\Dll\MyUtils.dll' WITH PERMISSION_SET = SAFE;

-- 3. 建立托管物件對應
CREATE PROCEDURE usp_CalcHash @input NVARCHAR(MAX)
AS EXTERNAL NAME MyUtils.[MyNamespace.MyClass].CalcHash;