SQL Server 特殊資料類型完解：timestamp、bit、uniqueidentifier、sql_variant、table

超越基礎類型：SQL Server 五大特殊資料類型深度解析與實戰場景

除了常用的數值、字串、日期時間，SQL Server 提供五種特殊類型解決特定領域問題。本文逐一剖析內部機制、適用場景與常見誤區。

1. timestamp / rowversion — 版本戳記而非時間

• 本質：資料庫層級遞增二進位計數器 (@@DBTS)，每修改一列自動更新
• 用途：樂觀並發控制 (比較更新前後值)、資料同步變更追蹤
• 關鍵誤區：不代表日期時間，不可用 CONVERT(DATETIME, ts)；rowversion 為同義詞 (建議新開發使用)
• 儲存：固定 8 位元組 varbinary(8)

2. bit — 布林邏輯的極致壓縮

• 值域：0、1、NULL (三值邏輯)
• 儲存優化：單表 ≤ 8 個 bit 欄位共享 1 位元組；9-16 個共 2 位元組，以此類推
• 慣例：1=True/Yes/啟用，0=False/No/停用 (勿反向，違背直覺)
• 索引陷阱：低選擇性 (僅 0/1)，單欄索引幾乎無效，需組合高選擇性欄位建立複合索引

3. uniqueidentifier (GUID) — 全域唯一識別碼

• 格式：16 位元組十六進位 (如 6F9619FF-8B86-D011-B42D-00C04FC964FF)
• 生成方式：
  • NEWID()：隨機 GUID，索引碎片極高，不宜作叢集索引鍵
  • NEWSEQUENTIALID()：有序 GUID (重啟後重置)，適合叢集索引，但可預測性較高
  • 應用層生成 (如 UUID v7、ULID) 兼具有序與不可預測
• 跨庫合併/分散式系統：天然適合，無需中央協調
• 儲存/效能：16 位元組 vs INT 4 位元組，Join/索引成本高 4 倍，權衡決策

4. sql_variant — 多型資料容器

• 支援類型：除 text/ntext/image/timestamp/sql_variant 外所有類型
• 結構：實際值 + 基底類型中繼資料 (佔額外空間)
• 適用場景：動態屬性表 (EAV 模式)、通用日誌欄位、異構資料暫存
• 限制：不可參與索引鍵、統計資料不準、隱含轉換陷阱，查詢需 SQL_VARIANT_PROPERTY 解析

5. table — 記憶體暫存結果集

• 用途：預存程序/函數內部暫存中間結果、傳回表值函數、替代臨時表 (#temp) 減少重編譯
• 宣告：

  DECLARE @t TABLE (ID INT PRIMARY KEY, Name NVARCHAR(50), CreateDate DATETIME DEFAULT GETDATE());

• 優勢：變數作用域自動清理、無鎖爭用 (單執行緒)、支援主鍵/唯一/預設約束
• 限制：無統計資料 (優化器假設 1 列)、大量資料會溢寫 tempdb、不可跨批次/動態 SQL 存取

使用者定義類型 (UDT) 簡介

透過 CREATE TYPE 封裝規則：

CREATE TYPE ProductCode FROM CHAR(7) NOT NULL;
-- 搭配 RULE/CHECK 约束: '^[A-Z]{2}\d{5}$'

實現領域驅動設計的值物件持久化，強化型別安全。

選型決策矩陣