SQL Server 資料壓縮（Data Compression）完全指南

目錄

• 什麼是資料壓縮
• 先分清三種「壓縮」別搞混
• 壓縮類型總覽
• ROW 壓縮原理
• PAGE 壓縮原理
• COLUMNSTORE 與 COLUMNSTORE_ARCHIVE
• 差異與選型
• 怎麼做：語法
• 估算與檢視現況
• 最佳實踐與注意事項
• 常見問題
• 總結與速查

什麼是資料壓縮

SQL Server 的資料壓縮是在儲存引擎層，把資料以更省空間的格式存放。它省下的不只是磁碟：

• 磁碟空間：檔案更小、備份更小。
• 記憶體（buffer pool）：壓縮後的頁在記憶體裡仍維持壓縮狀態，同樣的記憶體能快取更多資料 → 命中率上升。
• I/O：要讀的頁變少，對 I/O-bound 的工作負載（大量掃描）常能反而變快。

代價是 CPU：讀取時要解壓、寫入時要壓縮。所以資料壓縮的本質是一組取捨——用 CPU 換磁碟、記憶體與 I/O。它在「I/O 是瓶頸、CPU 有餘裕」的系統最划算；反過來在 CPU 已吃緊、又高頻更新的表上可能得不償失。

壓縮率高度取決於資料特性：低基數、重複性高的欄位壓得很好；已隨機或加密的資料幾乎壓不動。所以永遠先估算（見後文）再決定。

先分清三種「壓縮」別搞混

SQL Server 有三個都叫「壓縮」但完全不同的東西，這是最常見的混淆來源：

本篇談的是第一種 DATA_COMPRESSION。看到別人的 SQL 用到 COMPRESS() 或備份壓縮時，別跟資料表壓縮混為一談。

壓縮類型總覽

DATA_COMPRESSION 的可選值分成兩大家族，取決於底層是列存放（rowstore）還是欄存放（columnstore）：

• NONE：不壓縮（預設）。
• ROW → PAGE：壓縮力道遞增、CPU 成本也遞增（PAGE 是 ROW 的超集）。
• COLUMNSTORE → COLUMNSTORE_ARCHIVE：欄存放的一般壓縮 → 更狠的封存壓縮。

以下逐一說明原理。

ROW 壓縮原理

ROW 壓縮的重點是去掉「固定長度型別」的浪費，用更精簡的列格式存放。主要手段：

• 定長型別改用變長儲存：int、bigint、decimal、char(n) 等，只用「實際需要的位元組數」存。例如 bigint 欄位存值 5，不再固定佔 8 bytes。
• NULL 與 0 幾乎不佔空間：只用 metadata 的位元標記。
• 減少每列的 metadata 負擔：採用更精簡的列記錄格式。

特性：壓縮率溫和、CPU 額外負擔低。適合「有很多沒用滿的定長欄位」的表，尤其在高頻更新、又想省一點空間時，ROW 通常是安全的起點。

範例：一個 bigint 欄位存 5
  未壓縮：08 00 00 00 00 00 00 00   （固定 8 bytes）
  ROW  ：05                         （只用 1 byte）

PAGE 壓縮原理

PAGE 壓縮是 ROW 的超集，在頁（8KB）層級再多做兩層。套用順序固定為：

① 先做 ROW 壓縮 → ② 前綴壓縮（prefix） → ③ 字典壓縮（dictionary）。

前綴壓縮（每欄，per-column）

在同一欄內找出可共用的前綴，存一次在頁首後方的壓縮資訊區（CI），各儲存格只記「用了前綴幾個字 + 差異部分」。

某欄的值：  AAAB   AAAC   AAAD
共用前綴：  AAA（存一次）
壓縮後：    [3]B   [3]C   [3]D    （3 = 取前綴前 3 個字）

字典壓縮（整頁，per-page）

前綴處理後，再跨整頁（不分欄）找重複出現的值，存進一份頁內字典，出現處改成指向字典的參照。

整頁多處都出現值 "AAAB"
  → 字典存一份 "AAAB"
  → 各出現處改成「→字典項#1」

特性：壓縮率高、CPU 成本也較高。頁壓縮是在頁被填滿時才評估套用（heap 的新頁多半要等重建或 TABLOCK 大量匯入才會真正頁壓縮）。適合重複性高、以掃描為主、更新不頻繁的資料。

COLUMNSTORE 與 COLUMNSTORE_ARCHIVE

前兩種是列存放的壓縮；欄存放索引（columnstore index） 走的是另一套。欄存放把資料按欄存放，同一欄的值型別一致、重複性高，天生極度適合壓縮，配合批次模式（batch mode）執行，是分析 / 資料倉儲（DW）大表的首選。

• COLUMNSTORE：欄存放索引的預設壓縮，壓縮率通常遠高於 rowstore 的 PAGE。
• COLUMNSTORE_ARCHIVE：在此之上再加一層更強的封存壓縮，檔案更小、但壓/解壓更耗 CPU。適合很少被查詢的冷資料——典型做法是只對舊分割套用（見下方 per-partition 範例）。

本篇把這兩型當作「DATA_COMPRESSION 的其中兩個值」帶到「是什麼 / 何時用 / 怎麼設」的層級。欄存放索引本身的內部結構（rowgroup、segment、delta store、批次模式）是另一個大主題，不在此展開。

差異與選型

選型準則（微軟官方建議的方向）：看這張表 / 索引是掃描多還是更新多：

• 掃描（scan）占比高、更新占比低 → 傾向 PAGE（讀取受惠、壓縮率好）。
• 更新（update）占比高 → 傾向 ROW 或不壓（避免 PAGE 反覆重壓的 CPU 成本）。
• 分析型大表 → 欄存放；其中冷分割再上 COLUMNSTORE_ARCHIVE。

可用 sys.dm_db_index_operational_stats 觀察掃描 vs 更新的比例，作為判斷依據。

怎麼做：語法

建立時就指定

-- 建表（heap / 叢集索引的資料）
CREATE TABLE dbo.Orders (...) WITH (DATA_COMPRESSION = PAGE);

-- 建索引
CREATE INDEX IX_Orders_Date ON dbo.Orders (order_date)
WITH (DATA_COMPRESSION = ROW);

對既有物件套用（用 REBUILD）

壓縮既有資料是重建（rebuild） 操作：

-- 壓縮 heap 或叢集索引（= 整張表的資料層）
ALTER TABLE dbo.Orders REBUILD WITH (DATA_COMPRESSION = PAGE);

-- 壓縮單一索引
ALTER INDEX IX_Orders_Date ON dbo.Orders REBUILD WITH (DATA_COMPRESSION = PAGE);

-- 壓縮該表所有索引
ALTER INDEX ALL ON dbo.Orders REBUILD WITH (DATA_COMPRESSION = PAGE);

-- 線上重建，降低鎖定（ONLINE 為 Enterprise 版功能）
ALTER INDEX ALL ON dbo.Orders REBUILD WITH (DATA_COMPRESSION = PAGE, ONLINE = ON);

重點：非叢集索引有各自獨立的壓縮設定，壓縮資料表 / 叢集索引不會連帶壓縮非叢集索引，要各自處理（或用 ALTER INDEX ALL）。

每個分割獨立設定（搭配資料表分割）

壓縮的粒度是「每索引 × 每分割」，可以讓熱分割用 ROW、冷分割用 PAGE 或封存壓縮：

-- 熱分割（1~3）用 PAGE，最舊的冷分割（4）改用欄存放封存壓縮
ALTER TABLE dbo.Orders
REBUILD PARTITION = ALL WITH (
  DATA_COMPRESSION = PAGE ON PARTITIONS (1 TO 3),
  DATA_COMPRESSION = COLUMNSTORE_ARCHIVE ON PARTITIONS (4)
);

這正是壓縮與SQL Server 資料表分割的天然接點：冷熱分層 + 分層壓縮。

估算與檢視現況

先估算能省多少（套用前）

sp_estimate_data_compression_savings 會回報「目前大小 vs 套用某壓縮後的預估大小」，不會真的改資料：

-- 參數：schema, 物件, index_id(NULL=全部), partition(NULL=全部), 目標壓縮
EXEC sp_estimate_data_compression_savings
     'dbo', 'Orders', NULL, NULL, 'PAGE';

比較 size_with_current_compression_setting(KB) 與 size_with_requested_compression_setting(KB) 就知道值不值得。

查目前壓縮狀態

SELECT OBJECT_NAME(p.object_id) AS table_name,
       i.name                    AS index_name,
       p.partition_number,
       p.data_compression_desc
FROM sys.partitions p
JOIN sys.indexes i
  ON i.object_id = p.object_id AND i.index_id = p.index_id
WHERE p.object_id = OBJECT_ID('dbo.Orders')
ORDER BY i.index_id, p.partition_number;

最佳實踐與注意事項

1. 先估再壓：用 sp_estimate_data_compression_savings 確認效益；重複性低的資料可能省不多卻白付 CPU。
2. 依工作負載選型：掃描多→PAGE、更新多→ROW/NONE、分析→columnstore；用 sys.dm_db_index_operational_stats 佐證。
3. rebuild 是 size-of-data 操作：需要足夠可用空間、會產生大量交易記錄、耗時；正式環境注意時段與 log 成長（見SQL Server 交易記錄檔管理）。
4. 索引要各自壓：非叢集索引不會被連帶壓縮。
5. 善用分割做分層壓縮：熱分割輕壓、冷分割重壓 / 封存。
6. LOB / off-row 資料不在範圍：ROW/PAGE 只壓 in-row 資料；大字串 / LOB 需用 COMPRESS() 或設計層處理。
7. 版本：SQL Server 2016 SP1 起，ROW/PAGE/columnstore 壓縮在所有 edition 都可用（之前限 Enterprise）；但 ONLINE 重建仍是 Enterprise 功能。

常見問題

問題 1：ROW 和 PAGE 怎麼選？

PAGE 是 ROW 的超集，壓縮率更高但 CPU 更貴。更新頻繁的表選 ROW（或不壓）較安全；掃描為主、更新少、重複性高的表選 PAGE。拿不定主意時先 sp_estimate_data_compression_savings 兩個都估，再對照工作負載。

問題 2：壓縮會讓查詢變慢還是變快？

看瓶頸在哪。若是 I/O-bound（大量掃描、記憶體不夠快取），壓縮讓要讀的頁變少、快取更多，常變快；若是 CPU-bound 又高頻存取，解壓的 CPU 成本可能讓它變慢。所以要看系統實際瓶頸。

問題 3：壓縮後在記憶體裡也是壓縮的嗎？

是。壓縮頁在 buffer pool 中維持壓縮狀態，存取到某列時才解壓那部分。這就是壓縮能同時省記憶體的原因，代價是每次存取的 CPU。

問題 4：壓縮資料表為什麼非叢集索引沒變小？

因為每個索引有獨立的壓縮設定。壓縮 heap / 叢集索引只影響資料層，非叢集索引要另外 ALTER INDEX ... REBUILD WITH (DATA_COMPRESSION = ...)，或用 ALTER INDEX ALL。

問題 5：DATA_COMPRESSION 和 COMPRESS() 一樣嗎？

不一樣。DATA_COMPRESSION 是儲存引擎對整表 / 索引 / 分割的行/頁/欄式壓縮；COMPRESS() 是 T-SQL 函式，對單一 varbinary 值做 GZIP，通常用來壓 LOB / 大字串欄位。兩者用途、粒度都不同。

總結與速查

核心要點

• 資料壓縮＝用 CPU 換磁碟 / 記憶體 / I/O；在 I/O 為瓶頸、CPU 有餘裕時最划算。
• 五個值分兩家族：rowstore 的 NONE/ROW/PAGE、columnstore 的 COLUMNSTORE/COLUMNSTORE_ARCHIVE。
• PAGE = ROW + 前綴 + 字典；力道與 CPU 都比 ROW 高。
• 選型看掃描 vs 更新：掃描多→PAGE、更新多→ROW/NONE、分析→columnstore、冷資料→archive。
• 粒度是每索引 × 每分割；套用既有資料要 REBUILD（size-of-data 操作）。

指令速查

延伸

• 冷熱分層 + 分層壓縮：見 SQL Server 資料表分割。
• rebuild 造成的記錄檔成長：見 SQL Server 交易記錄檔管理。

建立日期：2026-07-03