SQL Server 2008 資料壓縮功能完整解析：行壓縮、頁面壓縮與分區策略

SQL Server 2005 SP2 引入 vardecimal 儲存格式，號稱能為資料倉庫節省 30% 空間。SQL Server 2008 更進一步，正式推出 資料壓縮 功能，支援 行壓縮 (ROW) 與 頁面壓縮 (PAGE) 兩種層級，並可套用至資料表、索引、索引檢視表及分區物件。本文深入探討壓縮原理、效益分析與實作語法。

一、 可套用壓縮的物件範圍

• 堆疊資料表 (無叢集索引)
• 叢集索引資料表
• 非叢集索引 (需個別設定，資料表壓縮不自動延伸至非叢集索引)
• 索引檢視表
• 分區資料表與分區索引的單一分區

二、 為何需要資料壓縮？三大核心效益

1. 降低管理成本

資料庫體積縮小 → 備份時間縮短、備份媒體成本降低、DBCC 維護作業耗時減少。

2. 提升查詢效能

SQL Server 以「資料頁」(8KB) 為單位讀取磁碟。壓縮後單一頁面容納更多資料列 → 減少 I/O 次數 → 提升掃描效能。CPU 解壓縮成本遠低於磁碟 I/O 等待。

3. 節省儲存硬體成本

官方測試資料壓縮率達 2X~7X。結合異地備援儲存需求，硬體採購成本大幅降低。

三、 壓縮類型選擇指南

四、 實作語法大全

1. 建立資料表時指定壓縮

CREATE TABLE TestTable (col1 int, col2 varchar(200))
WITH (DATA_COMPRESSION = ROW);

2. 修改現有資料表/索引壓縮設定 (需 REBUILD)

-- 整張表
ALTER TABLE TestTable REBUILD WITH (DATA_COMPRESSION = PAGE);

-- 單一分區
ALTER TABLE TestTable REBUILD PARTITION = 1 WITH (DATA_COMPRESSION = PAGE);

-- 索引
CREATE INDEX IX_TestTable_Col1 ON TestTable (Col1) WITH (DATA_COMPRESSION = ROW);
ALTER INDEX IX_TestTable_Col1 ON TestTable REBUILD WITH (DATA_COMPRESSION = ROW);

3. 分區資料表差異化壓縮策略 (關鍵進階功能)

資料倉庫事實表常有「熱分區」(近期資料頻繁更新) 與「冷分區」(歷史唯讀)。SQL Server 2008 允許分區層級指定不同壓縮：

CREATE TABLE PartitionedTable (col1 int, col2 varchar(200))
ON PS1 (col1)
WITH (
    DATA_COMPRESSION = ROW ON PARTITIONS(1),      -- 熱分區：行壓縮，低 CPU 開銷
    DATA_COMPRESSION = PAGE ON PARTITIONS(2 TO 4) -- 冷分區：頁面壓縮，最高空間節省
);

五、 壓縮效益評估工具

執行前可用系統預存程序預估空間節省：

EXEC sp_estimate_data_compression_savings
    @schema_name = 'dbo',
    @object_name = 'TestTable',
    @index_id = NULL, -- NULL 為堆疊/叢集索引
    @partition_number = 1,
    @data_compression = 'PAGE';

六、 注意事項與限制

• 壓縮不支援 FILESTREAM、Columnstore 索引 (SQL 2012+)、記憶體最佳化資料表。
• PAGE 壓縮會增加 CPU 負載，寫入密集型工作負載請謹慎評估。
• 啟用壓縮後，sp_spaceused 回報的 reserved 與 data 大小即為壓縮後大小。