官方文件
https://learn.microsoft.com/zh-cn/sql/relational-databases/data-compression/data-compression?view=sql-server-ver16
https://learn.microsoft.com/zh-cn/sql/t-sql/functions/compress-transact-sql?view=sql-server-ver16

結論：

1、Sqlserver支援針對行儲存表和索引的行和頁壓縮，並且支援針對列儲存表和索引的列儲存和列儲存存檔壓縮。對於行儲存表和索引，使用資料壓縮功能可幫助減小資料庫的大小。 除了節省空間之外，資料壓縮還可以幫助提高 I/O 密集型工作負荷的效能，因為資料儲存在更少的頁中，查詢需要從磁碟讀取的頁更少。 但是，在與應用程式交換資料時，在資料庫伺服器上需要額外的 CPU 資源來壓縮和解壓縮資料。 您可以在以下資料庫物件上配置行和頁壓縮：儲存為堆的整個表、儲存為聚集索引的整個表、整個非聚集索引、整個索引檢視、對於已分割槽表和已分割槽索引，可為每個分割槽配置壓縮選項，且物件的各個分割槽的壓縮設定不必相同。
2、壓縮功能不可用於系統表。
3、透過壓縮可在一頁上儲存更多的行，但不會更改表或索引的最大行大小。
4、當指定分割槽列表時，可以將各個分割槽的壓縮型別設定為 ROW、PAGE 或 NONE。 如果未指定分割槽列表，將使用語句中指定的資料壓縮屬性來設定所有分割槽。 建立表或索引時，除非指定了其他壓縮設定，否則資料壓縮將設定為 NONE。 修改表時，除非指定了其他壓縮設定，否則將保留現有壓縮設定。
5、當最大行大小加上壓縮開銷超過最大行大小 8060 個位元組時，不能對錶啟用壓縮功能。 例如，不能壓縮具有 c1 CHAR(8000) 和 c2 CHAR(53) 列的表，因為存在額外的壓縮開銷。
create table t12345 (c1 CHAR(8000) , c2 CHAR(50))--正常建立
create table t123456 (c1 CHAR(8000) , c2 CHAR(60))--無法建立，有報錯：Creating or altering table 't123456' failed because the minimum row size would be 8067, including 7 bytes of internal overhead. This exceeds the maximum allowable table row size of 8060 bytes.
6、非聚集索引不繼承表的壓縮屬性。 若要壓縮索引，必須顯式設定索引的壓縮屬性。 預設情況下，建立索引時，索引的壓縮設定會設定為NONE。
7、對堆表建立聚集索引時，聚集索引會繼承該堆表的壓縮狀態，除非指定了另一壓縮狀態。
8、如果堆表配置為頁級壓縮，則只有在以下情況下，頁才會進行頁級壓縮：
    在啟用大容量最佳化的情況下大容量匯入資料。
    資料是使用 INSERT INTO ... WITH (TABLOCK) 語法插入的，並且表沒有非聚集索引。
    表是透過執行帶 PAGE 壓縮選項的 ALTER TABLE ... REBUILD 語句重新生成的。
9、透過 DML 操作被分配到堆表中的新頁面不會使用 PAGE 壓縮，除非重新生成該堆。 重新生成堆的方法有：刪除壓縮然後重新應用壓縮，或者建立聚集索引然後再刪除聚集索引。
10、若要更改堆表的壓縮設定，要求對錶重新生成所有非聚集索引，以便它們具有指向堆表中的新行位置的指標。
11、可以聯機或離線啟用或禁用 ROW 或 PAGE 壓縮功能。 當執行聯機操作時，對堆啟用壓縮功能是單執行緒的。
12、啟用或禁用行壓縮或頁壓縮的磁碟空間要求與建立或重新生成索引時的磁碟空間要求相同。 對於已分割槽資料，可以透過每次對一個分割槽啟用或禁用壓縮功能來減少所需的空間。
13、壓縮索引時，可以使用行壓縮和頁壓縮來壓縮葉級頁。 非葉級頁不接收頁壓縮。
14、當使用頁壓縮時，將僅使用行壓縮來壓縮索引的非葉級別頁。
15、使用頁壓縮壓縮表和索引的葉級別的過程由按以下順序進行的三個操作組成：行壓縮、字首壓縮、字典壓縮。可以簡單理解：PAGE壓縮包括ROW壓縮，所以PAGE壓縮率更高

DATA_COMPRESSION = {NONE | ROW | PAGE | COLUMNSTORE | COLUMNSTORE_ARCHIVE}

NONE：不壓縮表或指定的分割槽。 此選項不適用於列儲存表。
ROW：使用行壓縮來壓縮表或指定的分割槽。 此選項不適用於列儲存表。
PAGE：使用頁壓縮來壓縮表或指定的分割槽。 此選項不適用於列儲存表。
COLUMNSTORE：適用於SQL Server（SQL Server 2014 (12.x) 及更高版本）和 Azure SQL 資料庫。僅適用於列儲存表。
COLUMNSTORE_ARCHIVE：適用於：SQL Server（SQL Server 2014 (12.x) 及更高版本）和 Azure SQL 資料庫。僅適用於列儲存表。

使用頁壓縮壓縮表和索引的葉級別的過程由按以下順序進行的三個操作組成：行壓縮、字首壓縮、字典壓縮

行壓縮
    減少了與記錄相關聯的後設資料開銷。 此後設資料為有關列、列長度和偏移量的資訊。 在某些情況下，後設資料開銷可能大於舊的儲存格式。
    它對於數值型別（例如， integer、 decimal和 float）和基於數值的型別（例如， datetime 和 money）使用可變長度儲存格式。
    它透過使用不儲存空字元的可變長度格式來儲存定長字串。
    將對所有資料型別的 NULL 和 0 值進行最佳化，從而使它們不佔用任何位元組。
    不受壓縮影響的欄位型別：tinyint、smalldatetime、date、time、varchar、text、nvarchar、ntext、varbinary、xml

字首壓縮
對於要壓縮的每一頁，字首壓縮採用以下步驟：
    對於每一列，將確定一個值，此值可用於減少每一列中的值的儲存空間。
    將建立表示每一列的字首值的行，並將其儲存在緊隨頁頭之後的壓縮資訊 (CI) 結構中。
    列中重複的字首值將由指向對應字首的引用進行替換。 如果行中的值與所選字首值並不完全匹配，則仍會指示存在部分匹配。

字典壓縮
字首壓縮完成後，將應用字典壓縮。 字典壓縮搜尋頁面上任意位置的重複值，然後將它們儲存在 CI 區域中。 與字首壓縮不同，字典壓縮不侷限於一列。 字典壓縮可以替換頁面上任意位置出現的重複值。

Sqlserver表和索引壓縮

相關文章