SQL Server 2000 的 `sp_addtype` 僅能建立**別名類型** (Alias Type):以現有型別為基礎加上 `NOT NULL`/`DEFAULT`/`RULE` 等約束,本質仍是基底型別。Yukon (SQL Server 2005) 引入 **CLR 使用者定義類型 (UDT)**,允許以 .NET 類別實作完整的結構化、行為豐富、可序列化、可比較、可索引的**第一類純量型別**。
[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));
}
CREATE ASSEMBLY GeoSpatial FROM 'C:\Bin\GeoSpatial.dll' WITH PERMISSION_SET = SAFE;
CREATE TYPE dbo.Point EXTERNAL NAME GeoSpatial.[Spatial.Point];
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;
| Format | 適用場景 | 限制 |
|---|---|---|
| Native | 簡單結構、全為基本型別 (int, double, bool 等) | 不支援陣列、物件參考、自訂版本 |
| UserDefined | 複雜結構、需版本控制、LOB、陣列 | 需實作 `IBinarySerialize`,MaxByteSize ≤ 8000 或 -1 (MAX) |