SQL Server 2008 稀疏欄位與欄位集深度解析

SQL Server 2008 引入稀疏欄位與欄位集兩大新特性，專為處理大量 NULL 值的資料模型設計。本文詳細說明其運作原理、適用場景與實作範例。

稀疏欄位：為 NULL 值優化儲存

稀疏欄位並非資料類型，而是欄位屬性（以 SPARSE 關鍵字宣告）。當欄位值為 NULL 時，不佔用實體儲存空間，極大降低稀疏資料表的磁碟佔用。但非 NULL 值會額外消耗 4 位元組開銷，且查詢效能可能受影響，需權衡取捨。

適用場景

• 實體擁有大量屬性欄位，但大多數記錄僅填寫少數屬性（典型的「屬性集」問題）。
• 動態結構資料，如產品目錄、問卷調查等。

欄位集：統一管理稀疏欄位的 XML 視圖

欄位集是 XML 資料類型的計算欄位，定義為 COLUMN_SET FOR ALL_SPARSE_COLUMNS。它將所有稀疏欄位聚合為單一 XML 結構，允許透過 XML 批次讀寫多個稀疏欄位，也可直接存取個別欄位。

完整建表與操作範例

1. 僅使用稀疏欄位

USE AdventureWorks;
GO
CREATE TABLE DocumentStore (
    DocID INT PRIMARY KEY,
    Title VARCHAR(200) NOT NULL,
    ProductionSpecification VARCHAR(20) SPARSE NULL,
    ProductionLocation SMALLINT SPARSE NULL,
    MarketingSurveyGroup VARCHAR(20) SPARSE NULL
);
GO
INSERT DocumentStore (DocID, Title, ProductionSpecification, ProductionLocation)
VALUES (1, 'Tire Spec 1', 'AXZZ217', 27);
INSERT DocumentStore (DocID, Title, MarketingSurveyGroup)
VALUES (2, 'Survey 2142', 'Men 25 - 35');

2. 結合欄位集

CREATE TABLE DocumentStoreWithColumnSet (
    DocID INT PRIMARY KEY,
    Title VARCHAR(200) NOT NULL,
    ProductionSpecification VARCHAR(20) SPARSE NULL,
    ProductionLocation SMALLINT SPARSE NULL,
    MarketingSurveyGroup VARCHAR(20) SPARSE NULL,
    MarketingProgramID INT SPARSE NULL,
    SpecialPurposeColumns XML COLUMN_SET FOR ALL_SPARSE_COLUMNS
);
GO
-- 直接寫入個別稀疏欄位
INSERT DocumentStoreWithColumnSet (DocID, Title, ProductionSpecification, ProductionLocation)
VALUES (1, 'Tire Spec 1', 'AXZZ217', 27);
-- 透過欄位集 XML 批次寫入
INSERT DocumentStoreWithColumnSet (DocID, Title, SpecialPurposeColumns)
VALUES (3, 'Tire Spec 2', '<ProductionSpecification>AXW9R411</ProductionSpecification><ProductionLocation>38</ProductionLocation>');

查詢行為差異

SELECT * 預設不回傳稀疏欄位，僅回傳欄位集 XML。若需取得個別稀疏欄位，必須在 SELECT 列表中明確指定：

SELECT DocID, Title, ProductionSpecification, ProductionLocation
FROM DocumentStoreWithColumnSet
WHERE ProductionSpecification IS NOT NULL;

更新操作

更新同樣支援兩種方式：直接更新稀疏欄位，或透過修改欄位集 XML 完成，效果等價。

效能與限制考量

• 稀疏欄位不支援預設值、IDENTITY、TIMESTAMP、使用者定義型別等。
• 欄位集僅能定義一個，且必須包含所有稀疏欄位（ALL_SPARSE_COLUMNS）。
• 大量稀疏欄位的表，建立索引時需謹慎規劃。

善用稀疏欄位與欄位集，能靈活應對半結構化資料，大幅節省儲存成本。