SQL Server 2005 引入 CLR 整合，允許使用 C#、VB.NET 等 .NET 語言撰寫預存程序、函數、觸發器、使用者定義型別/彙總。但 CLR 非萬靈丹，本文釐清 5 大常見問題，提供決策框架協助判斷何時該用 CLR、何時留在 T-SQL。
核心優勢：
@@ERROR/TRY...CATCHxp_cmdshell/OLE Automation (高風險)，CLR 原生支援且受程式碼存取安全性 (CAS) 管控| 適合 (✓) | 不適合 (✗) |
|---|---|
| 大量資料集上複雜計算/轉換 | 單純封裝複雜 SELECT/查詢邏輯 |
| 字串解析、正則、格式化 | 純集合運算 (JOIN、GROUP BY、聚合) |
| 需存取外部資源 (檔案、Web、AD) | 簡單 CRUD、參數化查詢 |
| 自訂彙總函數 (中位數、標準差等) | 已有高效 T-SQL 實作且效能足夠 |
| 程式邏輯複雜、需單元測試、版控 | 臨時腳本、一次性維護作業 |
黃金法則：若 SP 只是「執行 SQL 查詢」→ 留在 T-SQL (預編譯、最佳化器支援)。若 SP 需「在資料上跑複雜邏輯」→ 考慮 CLR。
-- T-SQL SP: 負責資料存取、篩選、回傳大量資料集
CREATE PROC dbo.GetLargeDataset @FilterParam INT
AS
SELECT * FROM BigTable WHERE FilterCol = @FilterParam;
GO
-- CLR SP: 呼叫 T-SQL 取得資料，在記憶體中做複雜處理
[Microsoft.SqlServer.Server.SqlProcedure]
public static void ProcessLargeDataset(SqlInt32 filterParam)
{
using (SqlConnection conn = new SqlConnection("context connection=true"))
{
SqlCommand cmd = new SqlCommand("dbo.GetLargeDataset", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@FilterParam", filterParam);
conn.Open();
using (SqlDataReader r = cmd.ExecuteReader())
{
// 複雜計算、機器學習推論、報表產生...
while (r.Read()) { /* ... */ }
}
}
}
優點：T-SQL 享有查詢最佳化、計畫快取；CLR 專注商務邏輯運算；資料轉換效能最佳 (SqlDataReader → CLR 物件零拷貝)。
決策流程：
可行但不建議：
csc.exe 編譯 → CREATE ASSEMBLY FROM 0x... 十六進位字串 → CREATE PROCEDURE ... EXTERNAL NAME| 權限集 | 允許操作 | 適用場景 | 風險 |
|---|---|---|---|
| SAFE (預設) | 純運算、記憶體、內部資料存取 | 函數、型別、純邏輯 SP | 最低 |
| EXTERNAL_ACCESS | 檔案、網路、Web Service、登錄 | 需外部資源的 SP | 中 (需資料庫 TRUSTWORTHY 或簽署) |
| UNSAFE | 非受控碼、P/Invoke、執行緒控制 | 極少數底層整合 | 高 (需 sysadmin 建立組件) |
[ ] 專案使用 SSDT / VS SQL Server 專案範本
[ ] 程式碼經過單元測試 (MSTest/xUnit + 測試資料庫)
[ ] 選擇最低權限集 (優先 SAFE)
[ ] 組件簽署強名稱金鑰 (SN.exe) 或資料庫 TRUSTWORTHY ON
[ ] 部署腳本包含 CREATE ASSEMBLY / CREATE PROCEDURE ... EXTERNAL NAME
[ ] 產前環境完整回歸測試 (效能、功能、並行)
[ ] 文件化：組件名稱、版本、權限集、對應 .NET Framework 版本
