萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> SQL Server 教學

SQL Server Yukon 託管 UDT:擴充型別系統的完整實作指南

從別名類型到真正的 UDT

SQL Server 2000 的 `sp_addtype` 僅能建立**別名類型** (Alias Type):以現有型別為基礎加上 `NOT NULL`/`DEFAULT`/`RULE` 等約束,本質仍是基底型別。Yukon (SQL Server 2005) 引入 **CLR 使用者定義類型 (UDT)**,允許以 .NET 類別實作完整的結構化、行為豐富、可序列化、可比較、可索引的**第一類純量型別**。

UDT 核心優勢

  • 型別系統平等:UDT 與 `INT`、`DATETIME` 同等地位,可作欄位、變數、參數、回傳值、索引鍵、計算欄位基礎。
  • 封裝與 OO 設計:狀態私有,透過公開方法/屬性操作,產生可重用組件。
  • 自訂序列化:`IBinarySerialize` 控制二進位格式,支援版本演進。
  • 可索引屬性/方法:對 UDT 公開屬性/方法建立計算欄位索引,查詢可直接 `WHERE Point.X > 100`。
  • 高階場景無縫支援:複寫、大量複製 (BCP)、分散式查詢、跨資料庫、備份/還原。

實作協定 (必要介面)

[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedType(
    Format.UserDefined,           // 序列化格式: UserDefined/ Native/ UserDefined
    IsByteOrdered = true,         // 位元組序是否等同邏輯排序 (可建立索引)
    IsFixedLength = false,        // 固定長度優化
    MaxByteSize = -1              // -1 = 無上限 (Max 8000 bytes)
)]
public struct Point : INullable, IBinarySerialize, IComparable
{
    // 1. INullable
    public bool IsNull { get; private set; }
    public static Point Null { get { return new Point { IsNull = true }; } }

    // 2. 屬性/欄位
    public double X { get; set; }
    public double Y { get; set; }

    // 3. 建構子/解析
    public static Point Parse(SqlString s)
    {
        if (s.IsNull) return Null;
        var parts = s.Value.Split(',');
        return new Point { X = double.Parse(parts[0]), Y = double.Parse(parts[1]) };
    }
    public override string ToString() => IsNull ? "NULL" : $"{X},{Y}";

    // 4. IBinarySerialize
    public void Read(BinaryReader r)
    {
        X = r.ReadDouble(); Y = r.ReadDouble(); IsNull = false;
    }
    public void Write(BinaryWriter w)
    {
        w.Write(X); w.Write(Y);
    }

    // 5. 比較 (支援索引排序)
    public int CompareTo(object obj) => /* 比較邏輯 */ 0;

    // 6. 自訂方法 (可用於查詢、索引)
    public double Distance(Point other) => Math.Sqrt(Math.Pow(X - other.X, 2) + Math.Pow(Y - other.Y, 2));
}

部署步驟

  1. **編譯** .NET 類別庫 (Target .NET Framework 2.0/3.5)。
  2. **註冊組件**:
    CREATE ASSEMBLY GeoSpatial FROM 'C:\Bin\GeoSpatial.dll' WITH PERMISSION_SET = SAFE;
  3. **建立 UDT**:
    CREATE TYPE dbo.Point EXTERNAL NAME GeoSpatial.[Spatial.Point];
  4. **使用**:
    CREATE TABLE Locations (ID INT PRIMARY KEY, Coord dbo.Point);
    INSERT INTO Locations VALUES (1, CONVERT(dbo.Point, '121.5,25.0'));
    SELECT Coord.X, Coord.Y, Coord.Distance(CONVERT(dbo.Point, '121.6,25.1')) AS Dist
    FROM Locations WHERE Coord.Distance(CONVERT(dbo.Point, '121.6,25.1')) < 10;

序列化格式選擇

修改與移除 UDT

  • `ALTER ASSEMBLY ... WITH UNCHECKED DATA`:更新組件程式碼 (需相容序列化格式)。
  • `DROP TYPE dbo.Point` → `DROP ASSEMBLY GeoSpatial`:移除順序固定,先移除相依物件 (表欄位、函數等)。

應用場景範例

  • **地理空間**:`Point`、`Polygon`、`Geometry` (SQL 2008+ 內建 `geography`/`geometry` 即此衍生)。
  • **金融**:`Money` (幣別+金額)、`InterestRate` (利率+複利規則)。
  • **加密資料**:`EncryptedString` (欄位級加密、金鑰管理封裝在 UDT 內)。
  • **工程量值**:`Measurement` (值+單位+公差、自動單位轉換)。

最佳實務提醒

  • **保持輕量**:UDT 實例儲存於資料列內,超過 8KB 會溢出至 LOB 頁面,影響效能。
  • **不可變設計**:偏好 `struct` + `readonly` 欄位,方法回傳新實例,利於並行與索引一致性。
  • **完整測試序列化往返**:`Write` → `Read` → `ToString` → `Parse` 循環不變。
  • **文件化公開合約**:屬性、方法、比較語意、排序規則,供 DBA/開發人員參考。
copyright © 萬盛學電腦網 all rights reserved
Format適用場景限制
Native簡單結構、全為基本型別 (int, double, bool 等)不支援陣列、物件參考、自訂版本
UserDefined複雜結構、需版本控制、LOB、陣列需實作 `IBinarySerialize`,MaxByteSize ≤ 8000 或 -1 (MAX)