善用 SQL Server 列存儲索引：打造數據倉庫查詢高速公路

列存儲索引：從 Denali 到生產環境的效能倍增器

SQL Server 2012（代號 Denali）引入的 Columnstore Index（列存儲索引），將資料按「欄」而非「列」儲存，配合高壓縮率與批次模式查詢處理，讓資料倉庫型工作負載的查詢速度提升 數十倍至百倍。本文深入解析其架構優勢、適用場景、限制與實務操作策略。

核心架構優勢

實測數據（8500 萬列測試表）

• 聚合+過濾查詢：列存儲索引完勝，行存儲僅在極少數點查場景略勝一籌。
• 查詢優化器自動選擇列存儲計畫，執行時間從 4 分鐘降至 1.2 秒（200 倍提升）。
• 建立索引耗時與傳統索引相當（CTP3 已優化建立速度）。
• 欄位重複值越多、資料型越簡單（int > varchar > decimal(38)），壓縮率越高、效能增益越大。

關鍵限制（SQL Server 2012/2014）

1. 每張表僅能建一個列存儲索引（2016 起支援多個）。
2. 建立後表變為「唯讀」，不可直接 INSERT/UPDATE/DELETE/MERGE/TRUNCATE。
3. 不支援資料類型：binary/varbinary/image/text/ntext/varchar(max)/nvarchar(max)/uniqueidentifier/timestamp/decimal(>18)/CLR/XML 等。
4. 分區表必須在索引中包含分區欄位。

繞過「唯讀」限制的三大實務策略

1. 定時重建法（適合夜間批次載入）

-- 每日維護窗口
DROP INDEX cci_FactSales ON FactSales;  -- 移除列存儲
-- 執行 ETL 載入/更新/刪除
CREATE CLUSTERED COLUMNSTORE INDEX cci_FactSales ON FactSales;  -- 重建

2. 分區切換法（適合滾動視窗資料）

1. 新資料載入暫存表 → 建立列存儲索引。
2. ALTER TABLE ... SWITCH PARTITION 將新分區切入主表。
3. 舊分區若需更新：SWITCH OUT → 移除索引 → 更新 → 重建索引 → SWITCH IN。

3. 熱冷資料分離 + UNION ALL（適合頻繁更新的熱資料）

-- 冷資料表（列存儲）
CREATE CLUSTERED COLUMNSTORE INDEX cci_Sales_History ON Sales_History;

-- 熱資料表（傳統 B-tree，支援隨意寫入）
CREATE TABLE Sales_Current (...);

-- 查詢視圖
CREATE VIEW vw_Sales_All AS
SELECT * FROM Sales_History
UNION ALL
SELECT * FROM Sales_Current;

查詢優化器會自動對 Sales_History 走列存儲掃描，對 Sales_Current 走傳統求值，兼顧效能與寫入靈活性。

版本演進亮點

決策檢核表：何時該上列存儲？

• ☐ 資料表 > 100 萬列（較小表效能增益不明顯，維護成本較高）。
• ☐ 查詢模式以 全表掃描、範圍聚合、星狀聯結事實表 為主。
• ☐ 寫入頻率低（每日/每週批次）或可接受「熱冷分離」架構。
• ☐ 欄位多為數值、日期、低基數維度鍵（高壓縮潛力）。
• ☐ 企業版/開發版（標準版 2016+ 僅支援非叢集列存儲且唯讀）。

結語

列存儲索引是 SQL Server 邁向 HTAP（混合交易/分析處理）的關鍵基石。善用「熱冷分離 + 分區切換 + 叢集列存儲」，即可在保持 OLTP 寫入效能的同時，讓 OLAP 查詢跑上「高速公路」。