SQL Server 2008 資料壓縮功能深度解析：列壓縮、頁面壓縮與分區策略

為何需要資料壓縮？

• 管理成本：備份/還原/DBCC 時間隨資料量線性增長，壓縮後時間顯著縮短。
• 效能提升：單頁容納更多列 → 掃描 I/O 減少 → 緩衝區命中率提升 → 查詢加速。
• 儲存成本：雖磁碟便宜，但企業級儲存 (SAN/SSD/備份帶庫) 成本仍高，壓縮率 2x-7x 節省可觀。
• 記憶體效益：壓縮頁面在記憶體中同樣保持壓縮狀態 (SQL 2008 R2+)，有效擴大緩衝區容量。

兩種壓縮模式

可套用物件與粒度

• 堆積表、叢集索引、非叢集索引、索引檢視表
• 分區層級獨立設定：熱分區 (近期資料) 用 ROW/無壓縮，冷分區 (歷史資料) 用 PAGE，完美平衡效能與空間。

建立與修改語法

-- 建立表指定壓縮
CREATE TABLE FactSales (Id INT, DateKey INT, Amount MONEY) 
WITH (DATA_COMPRESSION = PAGE) ON PartScheme(DateKey);

-- 修改整個表/索引壓縮 (重建)
ALTER TABLE FactSales REBUILD WITH (DATA_COMPRESSION = ROW);
ALTER INDEX IX_FactSales_DateKey ON FactSales REBUILD WITH (DATA_COMPRESSION = PAGE);

-- 分區級差異化壓縮 (SQL 2008 企業版/開發版)
CREATE TABLE PartitionedFact (Id INT, DateKey INT, Data VARCHAR(200))
ON PartScheme(DateKey)
WITH (
    DATA_COMPRESSION = ROW ON PARTITIONS(1),           -- 最新分區 ROW
    DATA_COMPRESSION = PAGE ON PARTITIONS(2 TO 10),    -- 歷史分區 PAGE
    DATA_COMPRESSION = NONE ON PARTITIONS(11)          -- 歸檔分區無壓縮
);

-- 修改單一分區
ALTER TABLE PartitionedFact REBUILD PARTITION = 5 WITH (DATA_COMPRESSION = PAGE);

壓縮效益評估：sp_estimate_data_compression_savings

EXEC sp_estimate_data_compression_savings 
    @schema_name = 'dbo', @object_name = 'FactSales', 
    @index_id = NULL, @partition_number = NULL, 
    @data_compression = 'PAGE';
-- 回傳: size_with_current_compression_setting, size_with_requested_compression_setting, sample_size

最佳實務

1. 先在測試環境跑 sp_estimate_data_compression_savings 評估壓縮率與 CPU 影響。
2. OLTP 核心表優先 ROW；資料倉庫事實表/維度表用 PAGE。
3. 分區表**必須**實施分區級差異化壓縮策略。
4. 非叢集索引壓縮需單獨設定，不隨基底表自動繼承。
5. 壓縮後監控 sys.dm_db_index_physical_stats 頁面密度、碎片率。
6. 備份壓縮 (WITH COMPRESSION) 與資料壓縮**疊加效果**，建議同時啟用。

版本限制

資料壓縮僅支援 **Enterprise、Developer、Evaluation** 版本。Standard 版本僅支援 vardecimal (SQL 2005 SP2+)。