資料壓縮

適用于： SQL Server Azure SQL資料庫Azure SQL 受控執行個體

SQL Server、Azure SQL 資料庫和Azure SQL 受控執行個體支援資料列存放區資料表和索引的資料列和頁面壓縮，並支援資料行存放區資料表和索引的資料行存放區和資料行存放區封存壓縮。

如果是資料列存放區資料表和索引，使用資料壓縮功能有助於減少資料庫的大小。 除了節省空間之外，資料壓縮也有助於改善 I/O 密集型工作負載的效能，因為資料會儲存在更少的頁面中，而且查詢需要從磁碟讀取的頁面也變少了。 但是在與應用程式交換資料時，資料庫伺服器上需要額外的 CPU 資源來壓縮和解壓縮資料。 您可以針對下列資料庫物件來設定資料列和頁面壓縮：

• 儲存為堆積的整個資料表。
• 儲存為叢集索引的整個資料表。
• 整個非叢集索引。
• 整個索引檢視。
• 如果是分割資料表和索引，您可以針對每個資料分割設定壓縮選項，而物件的不同資料分割則不必擁有相同的壓縮設定。

如果是資料行存放區資料表和索引，所有資料行存放區資料表和索引一律都使用資料行存放區壓縮，而且使用者無法進行設定。 當您可負擔額外的時間和 CPU 資源來儲存及擷取資料時，使用資料行存放區封存壓縮會進一步減少資料大小。 您可以針對下列資料庫物件來設定資料行存放區封存壓縮：

• 整個資料行存放區資料表或整個叢集資料行存放區索引。 因為資料行存放區資料表會儲存成叢集資料行存放區索引，所以兩種方法有相同的結果。
• 整個非叢集資料行存放區索引。
• 如果是分割資料行存放區資料表和資料行存放區索引，您可以針對每個資料分割設定封存壓縮選項，而不同資料分割則不必擁有相同的封存壓縮設定。

注意

資料也可以使用 GZIP 演算法格式進行壓縮。 這是額外的步驟，最適合在封存舊資料進行長期儲存時壓縮部分資料。 使用 COMPRESS 函式壓縮的資料無法編製索引。 如需詳細資訊，請參閱 COMPRESS (Transact-SQL) 。

資料列和頁面壓縮考慮

當您使用資料列和頁面壓縮時，請注意以下考量事項：

• Service Pack 或後續版本中的資料壓縮詳細資料可能會變更，恕不另行通知。

• 壓縮可在 Azure SQL 資料庫中使用

• 壓縮不適用於每個版本的 SQL Server。 如需詳細資訊，請參閱SQL Server 2016 版本支援的功能、SQL Server 2017 的版本和支援的功能，以及SQL Server 2019 的支援功能。

• 壓縮不適用於系統資料表。

• 壓縮可讓更多的資料列儲存在頁面上，但是不會變更資料表或索引的資料列大小上限。

• 當資料列大小上限加上壓縮負擔超過 8060 個位元組的資料列大小上限時，資料表將無法啟用壓縮。 例如，由於額外壓縮負荷的緣故，所以具有資料行 c1 CHAR(8000) 和 c2 CHAR(53) 的資料表無法加以壓縮。 當使用 Vardecimal 儲存格式時，將會在啟用此格式時執行資料列大小檢查。 對於資料列和頁面壓縮而言，最初壓縮物件時會執行資料列大小檢查，然後在插入或修改每一個資料列時加以檢查。 壓縮會強制執行下列兩個規則：

  • 固定長度類型的更新一定要成功。
  • 停用資料壓縮一定要成功。 即使壓縮的資料列符合頁面，這表示其小於 8060 個位元組;SQL Server可防止在資料列未壓縮時符合的更新。

• 啟用資料壓縮時，不會壓縮資料列資料。 例如，大於 8060 個位元組的 XML 記錄會使用未壓縮的資料列外頁面。 - 數種資料類型不受資料壓縮影響。 如需詳細資訊，請參閱 資料列壓縮如何影響儲存體。

• 當指定了資料分割清單時，壓縮類型可以在個別資料分割上設定為 ROW、PAGE 或 NONE。 如果未指定資料分割的清單，將會設定所有資料分割，並包含陳述式中所指定的資料壓縮屬性。 在建立資料表或索引時，除非另外指定，否則資料壓縮會設定為 NONE。 在修改資料表時，除非另外指定，否則會保留現有的壓縮。

• 如果您指定資料分割清單或超出範圍的資料分割，則會產生錯誤。

• 非叢集索引不會繼承資料表的壓縮屬性。 若要壓縮索引，您必須明確設定索引的壓縮屬性。 根據預設，當建立索引時，索引的壓縮設定會設定為 NONE。

• 在堆積上建立叢集索引時，此叢集索引會繼承堆積的壓縮狀態，除非指定了替代的壓縮狀態。

• 當堆積設定了頁面層級壓縮時，頁面只會以下列方式接收頁面層級壓縮：

  • 資料會在啟用大量最佳化的情況下大量匯入。
  • 使用 INSERT INTO ... WITH (TABLOCK) 語法來插入資料，此資料表沒有非叢集索引。
  • 執行 ALTER TABLE ... REBUILD 陳述式並指定 PAGE 壓縮選項來重建資料表。

• 重建堆積之前，配置在堆積中成為 DML 作業一部分的新頁面不會使用 PAGE 壓縮。 您可以透過移除並重新套用壓縮，或建立並移除叢集索引，重建堆積。

• 變更堆積的壓縮設定需要重建資料表上的所有非叢集索引，好讓它們擁有指向堆積內新資料列位置的指標。

• 您可以在線上或離線時啟用或停用 ROW 或 PAGE 壓縮。 在堆積上啟用壓縮對於線上作業而言是單一執行緒的作業。

• 啟用或停用資料列或頁面壓縮的磁碟空間需求與建立或重建索引的需求相同。 對於分割的資料而言，您可以一次啟用或停用一個資料分割的壓縮來減少所需的空間。

• 若要判斷資料分割資料表中資料分割的壓縮狀態，請查詢 data_compression 目錄檢視的資料 sys.partitions 行。

• 當您壓縮索引時，可以在壓縮資料列和頁面的情況下壓縮分葉層級頁面。 非分葉層級頁面不會收到頁面壓縮。

• 由於大數值資料類型的大小之緣故，這些類型有時會單獨儲存在特殊用途的頁面上，與一般資料列的資料分開。 資料壓縮不適用於個別儲存的資料。

• 在 SQL Server 2005 (9.x) 中實作vardecimal儲存格式的資料表，會在升級時保留該設定。 您可以將資料列壓縮套用至具有 vardecimal 儲存格式的資料表。 不過，因為資料列壓縮是 vardecimal 儲存格式的超集合，所以沒有理由保留 vardecimal 儲存格式。 當您將 vardecimal 儲存格式與資料列壓縮結合時，十進位值不會取得額外的壓縮。 您可以將頁面壓縮套用至具有 vardecimal 儲存格式的資料表;不過， vardecimal 儲存格式資料行可能不會達到額外的壓縮。

  注意

  所有支援的 SQL Server 版本都支援vardecimal儲存格式;不過，因為資料壓縮達到相同的目標，所以 vardecimal儲存格式已被取代。 未來的 Microsoft SQL Server 版本將移除這項功能。 請避免在新的開發工作中使用這項功能，並規劃修改目前使用這項功能的應用程式。

資料行存放區和資料行存放區封存壓縮

資料行存放區資料表和索引永遠都會以資料行存放區壓縮形式來儲存。 您可以進一步減少資料行存放區資料的大小，只要設定稱為封存壓縮的額外壓縮即可。 若要執行封存壓縮，SQL Server對資料執行 Microsoft XPRESS 壓縮演算法。 您可以使用下列資料壓縮類型來新增或移除封存壓縮：

• 使用 COLUMNSTORE_ARCHIVE 資料壓縮，以封存壓縮來壓縮資料行存放區的資料。
• 使用 COLUMNSTORE 資料壓縮，將封存壓縮解壓縮。 產生的資料會持續以資料行存放區壓縮形式壓縮。

若要新增封存壓縮，請使用 ALTER TABLE (Transact-SQL) 或 ALTER INDEX (Transact-SQL) 搭配 REBUILD 選項和 DATA COMPRESSION = COLUMNSTORE_ARCHIVE。

例如：

ALTER TABLE ColumnstoreTable1
REBUILD PARTITION = 1 WITH (
    DATA_COMPRESSION = COLUMNSTORE_ARCHIVE
);

ALTER TABLE ColumnstoreTable1
REBUILD PARTITION = ALL WITH (
    DATA_COMPRESSION = COLUMNSTORE_ARCHIVE
);

ALTER TABLE ColumnstoreTable1
REBUILD PARTITION = ALL WITH (
    DATA_COMPRESSION = COLUMNSTORE_ARCHIVE ON PARTITIONS (2, 4)
);

若要移除封存壓縮並將資料還原至資料行存放區壓縮，請使用 ALTER TABLE (Transact-SQL) 或 ALTER INDEX (Transact-SQL) 搭配 REBUILD 選項和 DATA COMPRESSION = COLUMNSTORE。

例如：

ALTER TABLE ColumnstoreTable1
REBUILD PARTITION = 1 WITH (
     DATA_COMPRESSION = COLUMNSTORE
);

ALTER TABLE ColumnstoreTable1
REBUILD PARTITION = ALL WITH (
    DATA_COMPRESSION = COLUMNSTORE
);

ALTER TABLE ColumnstoreTable1
REBUILD PARTITION = ALL WITH (
    DATA_COMPRESSION = COLUMNSTORE ON PARTITIONS (2, 4)
);

下一個範例會在某些資料分割上將資料壓縮設定為資料行存放區，以及在其他資料分割上設定為資料行存放區封存。

ALTER TABLE ColumnstoreTable1
REBUILD PARTITION = ALL WITH (
    DATA_COMPRESSION = COLUMNSTORE
        ON PARTITIONS (4, 5),
    DATA COMPRESSION = COLUMNSTORE_ARCHIVE
        ON PARTITIONS (1, 2, 3)
);

效能

以封存壓縮壓縮資料行存放區索引會造成該索引的效能比沒有封存壓縮的資料行存放區索引還要慢。 只有當您可以負擔使用額外時間和 CPU 資源來壓縮及擷取資料時，才使用封存壓縮。

封存壓縮的好處就是減少儲存體，這對於不常存取的資料很實用。 例如，如果您每個月的資料都有一個資料分割，而您的大多數活動發生在最近的月份，您可以封存較舊的月份來減少儲存需求。

中繼資料

下列系統檢視表包含叢集索引之資料壓縮的相關資訊：

• sys.indexes (Transact-SQL) - 和 type_desc 資料 type 行包括 CLUSTERED COLUMNSTORE 和 NONCLUSTERED COLUMNSTORE。
• sys.partitions (Transact-SQL) - 和 data_compression_desc 資料 data_compression 行包括 COLUMNSTORE 和 COLUMNSTORE_ARCHIVE。

Transact-SQL) sp_estimate_data_compression_savings (程式也可以套用至資料行存放區索引。

對分割資料表和索引的影響

當您搭配分割資料表和索引使用資料壓縮時，請注意以下考量事項：

• 當使用 ALTER PARTITION 陳述式分割這些分割區時，兩個分割區都會繼承原始分割區的資料壓縮屬性。

• 當合併兩個資料分割時，所產生的資料分割會繼承目標資料分割的資料壓縮屬性。

• 若要切換資料分割，此資料分割的資料壓縮屬性必須符合資料表的壓縮屬性。

• 您可以使用兩種語法變化來修改分割資料表或索引的壓縮：

  • 下列語法只會重建參考的資料分割：

    ALTER TABLE <table_name>
    REBUILD PARTITION = 1 WITH (
        DATA_COMPRESSION = <option>
    );
    

  • 下列語法會將現有的壓縮設定用於任何未參考的資料分割，藉以重建整個資料表：

    ALTER TABLE <table_name>
    REBUILD PARTITION = ALL WITH (
        DATA_COMPRESSION = PAGE ON PARTITIONS(<range>),
        ...
    );
    

  資料分割索引會遵循使用 ALTER INDEX 的相同準則。

• 當卸除叢集索引時，除非修改了資料分割配置，否則對應的堆積資料分割會保留其資料壓縮設定。 如果資料分割配置有所變更，所有資料分割都會重建為未壓縮的狀態。 若要卸除叢集索引及變更資料分割配置，您需要執行以下步驟：

  1. 卸除叢集索引。
  2. 使用指定壓縮選項的 ALTER TABLE ... REBUILD 選項來修改資料表。

  在線上卸除叢集索引將會是非常快速的作業，因為只會移除叢集索引的上層。 當叢集索引卸載 ONLINE 時，SQL Server必須重建堆積兩次，一次用於步驟 1，一次用於步驟 2。

壓縮如何影響複寫

當您搭配複寫使用資料壓縮時，請注意以下考量事項：

• 當快照集代理程式產生最初的結構描述指令碼時，新的結構描述會將相同的壓縮設定用於資料表和它的索引。 不能只在資料表上啟用壓縮，而不在索引上啟用壓縮。

• 如果是異動複寫，發行項結構描述選項會判斷哪些相依的物件和屬性必須編寫指令碼。 如需詳細資訊，請參閱 sp_addarticle。

  散發代理程式在套用指令碼時，不會檢查是否有下層的訂閱者。 如果選取了壓縮的複寫，在下層訂閱者上建立資料表就會失敗。 如果是混合拓撲，請勿啟用壓縮的複寫。

• 如果是合併式複寫，發行集相容性層級會覆寫結構描述選項，並判斷所編寫指令碼的結構描述物件。

  在混合拓撲的情況下，如果它不必支援新的壓縮選項，則發行集相容性層級應該設定為下層的訂閱者版本。 如果需要的話，請於建立資料表之後在訂閱者上壓縮資料表。

下表顯示在複寫期間控制壓縮的複寫設定。

使用者意圖	複寫資料表或索引的資料分割配置	複寫壓縮設定	指令碼行為
複寫資料分割配置，以及在資料分割的訂閱者上啟用壓縮。	True	True	同時針對資料分割配置和壓縮設定編寫指令碼。
複寫資料分割配置，但是不壓縮訂閱者上的資料。	是	否	針對資料分割配置編寫指令碼，但是不針對資料分割的壓縮設定編寫指令碼。
不複寫資料分割配置，而且不壓縮訂閱者上的資料。	False	False	不針對資料分割或壓縮設定編寫指令碼。
如果所有資料分割都在發行者上壓縮，則壓縮訂閱者上的資料表，但是不複寫資料分割配置。	False	True	檢查所有資料分割是否啟用壓縮。 針對資料表層級上的壓縮編寫指令碼。

對其他SQL Server元件的影響

適用于： SQL Server Azure SQL資料庫Azure SQL 受控執行個體

壓縮會在儲存引擎中發生，且資料會呈現給處於未壓縮狀態SQL Server的大部分其他元件。 這樣會將壓縮對其他元件的影響限制為以下情況：

• 大量匯入及匯出作業
  • 當匯出資料時 (即使是原生格式)，資料為非壓縮資料列格式的輸出。 這可能會造成匯出的資料檔大小比來源資料大出許多。
  • 當匯入資料時，如果目標資料表已啟用壓縮，則儲存引擎會將資料轉換成壓縮的資料列格式。 這樣可能會造成 CPU 使用量增加 (相較於資料匯入未壓縮的資料表時)。
  • 將資料大量匯入具有頁面壓縮的堆積內時，大量匯入作業會在插入具有頁面壓縮的資料時，嘗試壓縮這些資料。
• 壓縮不會影響備份和還原。
• 壓縮不會影響記錄傳送。
• 資料壓縮與疏鬆資料行不相容。 因此，包含疏鬆資料行的資料表無法加以壓縮，也無法將疏鬆資料行加入至壓縮的資料表。
• 啟用壓縮可能會造成查詢計畫變更，因為系統會使用不同的頁數以及每頁不同的資料列數來儲存資料。

另請參閱

• 資料列壓縮實作
• 頁面壓縮實作
• Unicode 壓縮實作
• CREATE PARTITION SCHEME (Transact-SQL)
• CREATE PARTITION FUNCTION (Transact-SQL)
• CREATE TABLE (Transact-SQL)
• ALTER TABLE (Transact-SQL)
• CREATE INDEX (Transact-SQL)
• ALTER INDEX (Transact-SQL)