SQL Server 2005 (代號 Yukon) 寄宿 .NET Framework 2.0 CLR,允許以 C#、VB.NET、C++/CLI 撰寫預存程序、觸發器、使用者定義函數 (UDF)、使用者定義類型 (UDT) 及使用者定義彙總 (UDA)。本文整理白皮書重點,涵蓋開發、部署、偵錯、CLR vs T-SQL 選擇準則與 UDT 應用。
| 情境 | 建議 | 理由 |
| 集合式資料存取、複雜查詢、報表 | T-SQL | 查詢最佳化器、索引利用、平行處理、集合導向效能最佳 |
| 複雜程序邏輯、演算法、字串處理、正則表達式、加密 | CLR | 豐富 BCL、強型別、OOP、例外處理、效能優於 T-SQL 迴圈/游標 |
| 需存取外部資源 (檔案、Web Service、登錄) | CLR (EXTERNAL_ACCESS/UNSAFE) | T-SQL 無原生能力,`xp_cmdshell` 不安全 |
| 自訂資料類型 (地理空間、複數、貨幣、加密資料) | UDT (CLR) | 封裝狀態與行為、可索引屬性/方法、支援序列化 |
| 彙總邏輯非標準 (中位數、眾數、串聯字串) | UDA (CLR) | T-SQL 無法自訂彙總 |
原則:能用單一 `SELECT` 解決就用 T-SQL;需程序邏輯、外部存取、型別擴充時選 CLR。
使用者定義類型 (UDT) 深度解析
優勢
- 擴充純量型別系統:可作為欄位、變數、參數、回傳值、索引鍵。
- 封裝:狀態私有,透過方法/屬性存取。
- 可索引:對 UDT 方法/屬性建立計算欄位索引,加速查詢。
- 高階場景支援:複寫、大量複製、分散式查詢、跨資料庫。
實作協定 (必要介面)
- `INullable`:`IsNull` 屬性、`Null` 靜態屬性。
- `IBinarySerialize`:`Read(BinaryReader)`、`Write(BinaryWriter)` 自訂序列化。
- `ToString()` / `Parse(static)`:字串轉換 (隱含/顯式轉換)。
- 比較:實作 `IComparable` 或提供靜態 `Compare` 方法。
部署與使用範例
-- 1. 註冊組件
CREATE ASSEMBLY GeoSpatial FROM 'C:\GeoSpatial.dll' WITH PERMISSION_SET = SAFE;
-- 2. 建立 UDT
CREATE TYPE dbo.Point EXTERNAL NAME GeoSpatial.[Spatial.Point];
-- 3. 使用
CREATE TABLE Locations (ID INT PRIMARY KEY, Coord dbo.Point);
INSERT INTO Locations VALUES (1, CONVERT(dbo.Point, '121.5,25.0'));
SELECT Coord.Lat, Coord.Lng FROM Locations WHERE Coord.Distance(CONVERT(dbo.Point, '121.6,25.1')) < 10;
小結
CLR 整合大幅擴展 SQL Server 可程式性,將資料庫推向「物件關聯式」平台。善用 VS 偵錯、權限集 (SAFE/EXTERNAL_ACCESS/UNSAFE) 與 T-SQL/CLR 互補策略,可構建高效能、可維護、安全的資料庫應用。
copyright ©
萬盛學電腦網 all rights reserved