您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

深度解析 SQL Server 2008 資料壓縮：行壓縮與頁壓縮實戰指南

編輯：關於SqlServer

為什麼需要資料壓縮？

雖然儲存成本下降，但資料庫體積直接影響：

備份/還原時間：體積越大，備份窗口越長，磁帶/磁碟成本越高。
維護成本：索引重建、DBCC 檢查、統計更新皆隨體積增長而變慢。
查詢效能：SQL Server 以 8KB 頁為單位讀取。壓縮後單頁容納更多列，邏輯讀次數下降，緩衝區命中率上升，I/O 明顯減少。

SQL Server 2005 SP2 引入 `vardecimal` 僅針對 decimal/numeric。SQL Server 2008 全面擴展至所有定長型別，並提供行壓縮與頁壓縮兩種模式（Enterprise/Developer 版專屬）。

模式一：行壓縮

將定長型別（`char`、`int`、`float` 等）以變長格式儲存。如 `char(100)` 存 "SQL Server 2008" 僅佔 15 字元空間，節省 85%。
所有型別的 `NULL` 與 `0` 值優化為零位元組儲存。
不支援：`XML`、`BLOB`、`MAX` 型別（`varchar(max)`、`varbinary(max)` 等）。
CPU 開銷極低，適合大多數 OLTP 表。

模式二：頁壓縮（建立在行壓縮之上）

前綴壓縮：頁面內各欄位共同前綴提取儲存於頁首，後續列僅存差異部分。
字典壓縮：頁面內重複值建立字典，後續列以字典索引取代。
頁面獨立壓縮，前綴/字典存於頁內。頁面未滿時不壓縮（原子單位為頁）。
CPU 開銷較高，適合唯讀/少寫入的資料倉庫事實表、歷史分區。

壓縮選擇決策表

引擎限制

場景	建議	理由
OLTP 高頻寫入表	行壓縮	低 CPU、無字典維護、鎖衝突少
資料倉庫大型事實表	頁壓縮	重複率高、查詢為主、I/O 節省最大
混合載入/查詢分區表	分區級混合	熱分區行壓縮/關閉，冷分區頁壓縮
含 XML/BLOB/MAX 欄位	不可壓縮

實戰語法大全

1. 建表時指定

-- 行壓縮
CREATE TABLE Alpha (col1 INT, col2 CHAR(1000)) 
WITH (DATA_COMPRESSION = ROW);

-- 頁壓縮（分區表指定分區）
CREATE TABLE Alpha_Partition (col1 INT, col2 CHAR(1000))
ON PartitionScheme(col1)
WITH (DATA_COMPRESSION = PAGE ON PARTITIONS(1 TO 3));

2. 修改現有表/索引

-- 整張表改為頁壓縮
ALTER TABLE Alpha REBUILD WITH (DATA_COMPRESSION = PAGE);

-- 分區表僅修改特定分區
ALTER TABLE Alpha REBUILD PARTITION = 2 WITH (DATA_COMPRESSION = PAGE);

-- 索引壓縮
CREATE INDEX IX_Alpha_Col1 ON Alpha(Col1) WITH (DATA_COMPRESSION = ROW);
ALTER INDEX IX_Alpha_Col1 ON Alpha REBUILD WITH (DATA_COMPRESSION = ROW);

3. 分區表混合壓縮策略（資料倉庫關鍵）

CREATE TABLE PartitionedTable (col1 INT, col2 VARCHAR(200))
ON PS1(col1)
WITH (
    DATA_COMPRESSION = ROW ON PARTITIONS(1),           -- 熱分區：行壓縮
    DATA_COMPRESSION = PAGE ON PARTITIONS(2 TO 4)      -- 冷分區：頁壓縮
);

評估壓縮效益：sp_estimate_data_compression_savings

EXEC sp_estimate_data_compression_savings 
    @schema_name = 'dbo',
    @object_name = 'BigTable',
    @index_id = 1,  -- 0=堆疊, 1=叢集索引, >1=非叢集索引
    @data_compression = 'PAGE';  -- 或 'ROW'

輸出含 `size_with_current_compression_setting`、`size_with_requested_compression_setting`、`sample_size`，可量化空間節省與預估 CPU 影響。