SQL Server 特殊數據類型詳解：sql_variant、table、timestamp、用戶定義類型

為什麼需要特殊類型？

標準類型無法滿足：動態結構、臨時結果集封裝、版本控制、業務語義強類型化等場景。SQL Server 提供四大特殊類型填補空白。

1. sql_variant：萬能容器

特性

• 單列存儲任意基礎類型值（除 text/ntext/image/timestamp/sql_variant 外）。
• 每個值自帶元數據：基礎類型、最大長度、精度、小數位、排序規則。
• 存儲開銷：基礎值大小 + 元數據開銷（約 20-40 字節）。

典型場景：屬性包 / EAV 模型

CREATE TABLE dbo.Entity (
    EntityID INT PRIMARY KEY,
    EntityName NVARCHAR(100)
);

CREATE TABLE dbo.EntityProperty (
    EntityID INT REFERENCES dbo.Entity(EntityID),
    PropertyName NVARCHAR(100),
    PropertyValue SQL_VARIANT,  -- 存儲 INT, DATETIME, DECIMAL, NVARCHAR 等
    PRIMARY KEY (EntityID, PropertyName)
);

-- 插入不同類型屬性
INSERT INTO dbo.EntityProperty VALUES
(1, 'Age', 25),
(1, 'BirthDate', '1999-01-01'),
(1, 'Salary', 12345.67),
(1, 'IsActive', 1),
(1, 'Bio', N'Senior Developer');

-- 查詢元數據
SELECT 
    PropertyName,
    PropertyValue,
    SQL_VARIANT_PROPERTY(PropertyValue, 'BaseType') AS BaseType,
    SQL_VARIANT_PROPERTY(PropertyValue, 'Precision') AS Precision,
    SQL_VARIANT_PROPERTY(PropertyValue, 'Scale') AS Scale,
    SQL_VARIANT_PROPERTY(PropertyValue, 'MaxLength') AS MaxLength
FROM dbo.EntityProperty
WHERE EntityID = 1;

⚠️ 限制與避坑

• 不能直接用於 ORDER BY/GROUP BY/JOIN 涉及的列（需顯式 CAST）。
• 索引 sql_variant 列：索引鍵長度限制 900 字節，實際值超長插入失敗。
• ODBC/JDBC 驅動可能將其映射為 OBJECT/VARIANT，應用層需顯式類型轉換。
• 大量使用導致計劃緩存膨脹（不同類型值產生不同計劃）。

2. table 類型：表值參數與函數返回的核心

定義與聲明

-- 1. 用戶定義表類型 (UDTT) - 參數傳遞
CREATE TYPE dbo.OrderItems AS TABLE (
    ProductID INT NOT NULL,
    Qty INT NOT NULL CHECK (Qty > 0),
    Price DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (ProductID)
);

-- 2. 存儲過程接收表值參數
CREATE PROC dbo.usp_CreateOrder
    @CustomerID INT,
    @Items dbo.OrderItems READONLY  -- 必須 READONLY
AS
BEGIN
    DECLARE @OrderID INT;
    INSERT INTO dbo.Orders (CustomerID, OrderDate) VALUES (@CustomerID, GETDATE());
    SET @OrderID = SCOPE_IDENTITY();
    INSERT INTO dbo.OrderDetails (OrderID, ProductID, Qty, Price)
    SELECT @OrderID, ProductID, Qty, Price FROM @Items;
    SELECT @OrderID AS OrderID;
END

-- 3. 變量聲明 (僅限批次/函數內)
DECLARE @TempTable TABLE (Id INT PRIMARY KEY, Name NVARCHAR(50));
INSERT INTO @TempTable VALUES (1, 'A'), (2, 'B');
SELECT * FROM @TempTable;

優勢

• 批量操作零拼接：應用層構造 DataTable/DataReader 直接傳入，避免 XML/JSON 序列化或循環調用。
• 計劃復用：參數化查詢，單一計劃服務所有批次大小。
• 事務一致性：單次調用原子提交/回滾。

限制

• 僅支持 READONLY，存儲過程內不可修改參數表內容。
• 不支持 ALTER TABLE、CREATE INDEX、DECLARE CURSOR（除 READ_ONLY 游標）。
• 統計信息不自動創建，大表值參數可能產生次優計劃（可 OPTION (RECOMPILE)）。

3. timestamp / rowversion：並發控制的版本戳

本質

• 與時間無關：全局單調遞增的二進制計數器 (@@DBTS)，每次行修改賦值並遞增。
• rowversion 為標準別名（SQL Server 2005+ 推薦使用）。
• 每表僅能有一列，自動維護，不可手工更新。

核心用途：樂觀鎖

ALTER TABLE dbo.Products ADD Ver ROWVERSION NOT NULL;

-- 讀取
SELECT ProductID, Name, Price, Ver FROM dbo.Products WHERE ProductID = 100;

-- 更新時檢查版本
UPDATE dbo.Products
SET Price = 99.99, Ver = Ver + 1  -- Ver 自動更新，此處僅示意
WHERE ProductID = 100 AND Ver = @ExpectedVer;  -- 參數為讀取時的 Ver 值

⚠️ 禁忌

• 絕不可作主鍵/聚簇索引鍵/外鍵：值每次修改都變，導致索引重建、外鍵級聯更新風暴。
• 不可用於 WHERE Ver > @LastVer 類範圍查詢（業務語義無意義），僅用於等值比較。

4. 用戶定義類型 (UDT)：業務語義強類型化

CLR UDT (SQL Server 2005+)

參考文章 87576 GeoPoint 示例。將驗證邏輯、序列化、運算符重載封裝在 .NET 結構體中，數據庫層面原生支持：

CREATE TYPE dbo.GeoPoint EXTERNAL NAME MyAssembly.[Namespace.GeoPoint];
CREATE TABLE dbo.Stores (StoreID INT PRIMARY KEY, Location dbo.GeoPoint);
INSERT INTO dbo.Stores VALUES (1, '31.2304,121.4737'); -- 隱式調用 Parse
SELECT Location.Lat, Location.Lng FROM dbo.Stores;   -- 訪問屬性
SELECT Location.Distance('31.2304,121.4737') FROM dbo.Stores; -- 調用方法

T-SQL 別名類型 (輕量級)

-- 僅提供命名別名 + 默認值 + 規則/約束綁定
EXEC sp_addtype 'SSN', 'VARCHAR(11)', 'NOT NULL';
CREATE TABLE dbo.Employees (
    EmpID INT PRIMARY KEY,
    SSN SSN CONSTRAINT CHK_SSN CHECK (SSN LIKE '[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]')
);

選型決策表

特殊類型是 SQL Server 可編程性的延伸，用對地方是利器，濫用是累贅。掌握其存儲特性、計劃影響、客戶端映射差異，在架構評審時明確標註「為何選此特殊類型」，才能避免技術債務積累。