SQL Server 2005 引入 CLR (Common Language Runtime) 宿主，允許在數據庫引擎內部執行 .NET 組件（C#/VB.NET/F#），打破 T-SQL 在複雜計算、正則表達式、加密、文件操作、Web Service 調用等領域的短板。
SAFE/EXTERNAL_ACCESS/UNSAFE 精細控制組件權限，調用鏈全鏈路檢查。| 權限集 | 允許操作 | 典型場景 | 部署要求 |
|---|---|---|---|
SAFE | 純計算、內存操作、訪問上下文數據 | 字符串處理、數學計算、邏輯判斷、UDT/UDA | 無需 TRUSTWORTHY，默認推薦 |
EXTERNAL_ACCESS | 文件系統、網絡、註冊表、事件日誌、Web Service | 文件導入導出、HTTP API 調用、日誌寫入 | 數據庫 TRUSTWORTHY ON 或簽名登錄 |
UNSAFE | 非托管代碼調用、線程控制、靜態字段共享、P/Invoke | 極少數高性能互操作、底層庫封裝 | 簽名登錄 + sysadmin 創建組件 |
安全檢查鏈：調用者權限 → 組件權限集 → 目標資源權限 → 上游組件權限，任一環節失敗即終止調用，防止提權攻擊。
[SqlFunction(DataAccess = DataAccessKind.Read, FillRowMethodName = "SplitFillRow")]
public static IEnumerable SplitString(SqlString input, SqlString separator)
{
if (input.IsNull || separator.IsNull) yield break;
foreach (var s in input.Value.Split(new[] { separator.Value }, StringSplitOptions.RemoveEmptyEntries))
yield return s;
}
public static void SplitFillRow(object row, out SqlString value)
=> value = new SqlString((string)row);
[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedType(Format.UserDefined, MaxByteSize = 8000, IsByteOrdered = true)]
public struct GeoPoint : INullable, IBinarySerialize
{
public double Lat, Lng;
public bool IsNull => double.IsNaN(Lat);
public static GeoPoint Null => new GeoPoint { Lat = double.NaN, Lng = double.NaN };
public static GeoPoint Parse(SqlString s) { /* 解析 "lat,lng" */ }
public override string ToString() => $"{Lat},{Lng}";
public void Write(BinaryWriter w) { w.Write(Lat); w.Write(Lng); }
public void Read(BinaryReader r) { Lat = r.ReadDouble(); Lng = r.ReadDouble(); }
// 可添加距離計算方法，直接在 SQL 中調用：SELECT @p1.Distance(@p2)
}
public class AuditTrigger
{
[SqlTrigger(Name = "trg_Audit_DML", Target = "dbo.Orders", Event = "FOR INSERT, UPDATE, DELETE")]
public static void AuditDml()
{
var trigCtx = SqlContext.TriggerContext;
var action = trigCtx.TriggerAction.ToString(); // Insert/Update/Delete
var cmd = SqlContext.Pipe.Connection.CreateCommand();
cmd.CommandText = @"
INSERT INTO dbo.AuditLog (TableName, Action, XmlData, UserName, EventTime)
VALUES ('Orders', @Action, @Xml, @User, GETDATE())";
// 讀取 inserted/deleted 虛表構建 XML
cmd.Parameters.AddWithValue("@Action", action);
cmd.Parameters.AddWithValue("@Xml", BuildChangeXml());
cmd.Parameters.AddWithValue("@User", SqlContext.WindowsIdentity.Name);
cmd.ExecuteNonQuery();
}
}
ALTER ASSEMBLY + 依賴對象刷新。單連接支持多個並發 SqlDataReader，避免「連接池耗盡」或「巢狀循環開多連接」。
using (var conn = new SqlConnection(cs)) {
conn.Open();
using (var cmd1 = new SqlCommand("SELECT * FROM Customers", conn))
using (var r1 = cmd1.ExecuteReader()) {
while (r1.Read()) {
using (var cmd2 = new SqlCommand("SELECT * FROM Orders WHERE CustomerID=@id", conn)) {
cmd2.Parameters.AddWithValue("@id", r1["CustomerID"]);
using (var r2 = cmd2.ExecuteReader()) { /* 處理訂單 */ }
}
}
}
}
// 連接字符串需啟用：MultipleActiveResultSets=True
SqlConnection 自動提升：單連接單事務用 SqlTransaction（輕量）；跨連接/跨資源管理器自動提升至 System.Transactions.TransactionScope (DTC)。TransactionScope 管理資源管理器登記，SQL Server 2005 支持可提升登記，性能優於強制 DTC。Xml 類型：SqlXml、XmlReader 流式讀寫、XQuery/XPath 原生執行。UDT 類型：SqlConnection.GetUdtType() 直接映射 CLR 結構體。VarDecimal、Date/Time (2008+) 預備支持。釋放 UI/ASP.NET 線程，提高吞吐量，配合 async/await (4.5+) 體驗更佳。
XmlSerializer 將 UDT/查詢結果序列化為 XML，HTTP 端點直接返回。HttpWebRequest / WebClient（需 EXTERNAL_ACCESS），實現異構系統同步/異步集成。SqlFileStream (2008+) 前身為 CLR 流操作。| 治理項 | 標準 |
|---|---|
| 組件簽名 | 強名簽名 (snk) + 憑證簽名，登錄至 master 數據庫 |
| 權限集審批 | 默認 SAFE，EXTERNAL_ACCESS 需架構師簽署風險評估 |
| 版本管理 | 組件版本號語義化，ALTER ASSEMBLY ... WITH UNCHECKED DATA 謹慎使用 |
| 性能基線 | 上線前壓測：CPU/內存/線程池/GC Gen2 收集頻率，對比 T-SQL 實現 |
| 審計日誌 | 記錄 CREATE/ALTER/DROP ASSEMBLY、執行錯誤、安全異常 |
CLR 集成將 SQL Server 從關係存儲升級為可編程計算平台，ADO.NET 2.0 則提供了匹配的高性能訪問協議。合理使用——T-SQL 做集合、CLR 做計算、ADO.NET 做協調——能構建出既高性能又易維護的企業級數據應用。切記：權限最小化、邊界清晰化、版本可追溯 是生產環境長期穩定的基石。
