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

SQL Server 2008 資料壓縮功能深度解析：列/頁面壓縮、適用物件、DDL 語法、空間估算與注意事項

編輯：SQL Server 2008

SQL Server 2008 在 2005 SP2 vardecimal 基礎上全面強化資料壓縮，支援 **列壓縮 (ROW)** 與 **頁面壓縮 (PAGE)**，可套用至堆積、叢集索引、非叢集索引、索引檢視、分區表/索引的單一分區。本文系統介紹壓縮原理、啟用語法、sp_estimate_data_compression_savings 空間估算、企業版/開發版限制、分區差異化壓縮策略與操作注意事項。

一、為什麼需要資料壓縮？

維度	效益
管理成本	備份時間縮短、備份媒體成本降低、DBCC 檢查維護窗口縮減
效能提升	單一資料頁容納更多列 → 掃描 I/O 減少、緩衝池命中率提升、查詢回應加快
儲存成本	官方測試 2X-7X 壓縮比，配合容災備份空間節省可觀

二、壓縮類型與原理

1. 列壓縮 (ROW)

減少記錄中繼資料開銷 (欄位長度、位移資訊)。
數值型別 (int, decimal, float, datetime, money) 採用**變長儲存格式**。
固定長度字串 (char, nchar) 以**不儲存尾端空白**的變長格式儲存。

2. 頁面壓縮 (PAGE) = 列壓縮 + 前綴壓縮 + 字典壓縮

列壓縮 (同上)
前綴壓縮：每欄位找出共同前綴，存於頁首，列僅存差異後綴。
字典壓縮：頁面層級建立重複值字典，列以字典索引取代完整值。

三、啟用壓縮的 DDL 語法

1. 建立資料表時指定

CREATE TABLE TestTable (col1 INT, col2 VARCHAR(200))
WITH (DATA_COMPRESSION = ROW); -- 或 PAGE

2. 修改現有資料表/索引 (需重建)

-- 整張表 (含叢集索引)
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. 分區表差異化壓縮 (熱分區關閉、冷分區開啟)

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

四、空間節省估算：sp_estimate_data_compression_savings

EXEC sp_estimate_data_compression_savings
    @schema_name = 'dbo',
    @object_name = 'TestTable',
    @index_id = NULL,        -- NULL = 堆積/叢集索引
    @partition_number = NULL,
    @data_compression = 'PAGE';  -- 或 'ROW'

回傳目前大小、預估壓縮後大小、採樣列數等，供決策參考。

五、關鍵注意事項與限制

項目	說明
版本限制	僅 Enterprise Edition 與 Developer Edition 支援；Standard/Express 不支援。
單列 8060 位元組上限	壓縮不改變單列最大長度限制。
叢集索引繼承	已壓縮表上建立叢集索引預設繼承表壓縮選項。
堆積表頁面壓縮生效條件	僅 `BULK INSERT`、`INSERT ... WITH (TABLOCK)`、`ALTER TABLE ... REBUILD WITH (DATA_COMPRESSION = PAGE)` 才會實際套用頁面壓縮；一般 INSERT 不觸發。
變更壓縮導致非叢集索引重建	堆積表改變壓縮選項 → 所有非叢集索引自動重建 (資料列位置改變)。
暫存空間需求	重建壓縮所需暫存空間 ≈ 建立索引空間；分區表可逐分區重建降低峰值壓力。
vardecimal 取代	資料壓縮為 vardecimal 超集；SQL 2008 保留 vardecimal 相容，下版本將棄用，建議遷移至 DATA_COMPRESSION。
BULK LOAD/匯出影響	壓縮在儲存引擎層透明；大量匯入會增加 CPU 負載；匯出至外部檔案會解壓縮，檔案體積變大。