事件
3月31日 下午11時 - 4月2日 下午11時
最大的 SQL、網狀架構和 Power BI 學習事件。 3 月 31 日 - 4 月 2 日。 使用程式代碼 FABINSIDER 來節省 $400 美元。
立即註冊適用於:SQL Server
Azure SQL 資料庫
Azure SQL 受控執行個體
SQL Server、Azure SQL Database 和 Azure SQL 受控執行個體支援資料列和頁面壓縮 (針對資料列存放區的資料表和索引),亦支援資料行存放區和資料行存放區封存壓縮 (針對資料行存放區的資料表和索引)。
如果是資料列存放區資料表和索引,使用資料壓縮功能有助於減少資料庫的大小。 除了節省空間之外,資料壓縮也有助於改善 I/O 密集型工作負載的效能,因為資料會儲存在更少的頁面中,而且查詢需要從磁碟讀取的頁面也變少了。 但是在與應用程式交換資料時,資料庫伺服器上需要額外的 CPU 資源來壓縮和解壓縮資料。 您可以針對下列資料庫物件來設定資料列和頁面壓縮:
如果是資料行存放區資料表和索引,所有資料行存放區資料表和索引一律都使用資料行存放區壓縮,而且使用者無法進行設定。 當您可負擔額外的時間和 CPU 資源來儲存及擷取資料時,使用資料行存放區封存壓縮會進一步減少資料大小。 您可以針對下列資料庫物件來設定資料行存放區封存壓縮:
注意
資料也可以使用 GZIP 演算法格式進行壓縮。 這是額外的步驟,最適合在封存舊資料進行長期儲存時壓縮部分資料。 使用 COMPRESS
函式壓縮的資料無法編製索引。 如需詳細資訊,請參閱 COMPRESS (Transact-SQL)。
當您使用資料列和頁面壓縮時,請注意以下考量事項:
Service Pack 或後續版本中的資料壓縮詳細資料可能會變更,恕不另行通知。
可在 Azure SQL 資料庫中進行壓縮
每一個 SQL Server 版本中都無法使用壓縮。 如需詳細資訊,請參閱本節結尾的版本和支援的功能清單。
壓縮不適用於系統資料表。
壓縮可讓更多的資料列儲存在頁面上,但是不會變更資料表或索引的資料列大小上限。
當資料列大小上限加上壓縮負擔超過 8060 個位元組的資料列大小上限時,資料表將無法啟用壓縮。 例如,由於額外壓縮負荷的緣故,所以具有資料行 c1 CHAR(8000)
和 c2 CHAR(53)
的資料表無法加以壓縮。 當使用 vardecimal 儲存格式時,將會在啟用此格式時執行資料列大小檢查。 對於資料列和頁面壓縮而言,最初壓縮物件時會執行資料列大小檢查,然後在插入或修改每一個資料列時加以檢查。 壓縮會強制執行下列兩個規則:
啟用資料壓縮時,不會壓縮非資料列資料。 例如,大於 8060 個位元組的 XML 記錄會使用未壓縮的資料列外頁面。
有幾種資料類型不受資料壓縮的影響。 如需詳細資訊,請參閱資料列壓縮如何影響儲存體。
當指定了資料分割清單時,壓縮類型可以在個別資料分割上設定為 ROW
、PAGE
或 NONE
。 如果未指定資料分割的清單,將會設定所有資料分割,並包含陳述式中所指定的資料壓縮屬性。 在建立資料表或索引時,除非另外指定,否則資料壓縮會設定為 NONE。 在修改資料表時,除非另外指定,否則會保留現有的壓縮。
如果您指定資料分割清單或超出範圍的資料分割,則會產生錯誤。
非叢集索引不會繼承資料表的壓縮屬性。 若要壓縮索引,您必須明確設定索引的壓縮屬性。 根據預設,當建立索引時,索引的壓縮設定會設定為 NONE。
在堆積上建立叢集索引時,此叢集索引會繼承堆積的壓縮狀態,除非指定了替代的壓縮狀態。
當堆積設定了頁面層級壓縮時,頁面只會以下列方式接收頁面層級壓縮:
INSERT INTO ... WITH (TABLOCK)
語法來插入資料,此資料表沒有非叢集索引。ALTER TABLE ... REBUILD
陳述式並指定 PAGE
壓縮選項來重建資料表。重建堆積之前,配置在堆積中成為 DML 操作一部分的新頁面不會使用 PAGE
壓縮。 您可以透過移除並重新套用壓縮,或建立並移除叢集索引,重建堆積。
變更堆積的壓縮設定需要重建資料表上的所有非叢集索引,好讓它們擁有指向堆積內新資料列位置的指標。
您可以在線上或離線時啟用或停用 ROW
或 PAGE
壓縮。 在堆積上啟用壓縮對於線上作業而言是單一執行緒的作業。
啟用或停用資料列或頁面壓縮的磁碟空間需求與建立或重建索引的需求相同。 對於分割的資料而言,您可以一次啟用或停用一個資料分割的壓縮來減少所需的空間。
若要判斷資料分割資料表中資料分割的壓縮狀態,請查詢 sys.partitions
目錄檢視的 data_compression
資料行。
當您壓縮索引時,可以在壓縮資料列和頁面的情況下壓縮分葉層級頁面。 非分葉層級頁面不會收到頁面壓縮。
由於大數值資料類型的大小之緣故,這些類型有時會單獨儲存在特殊用途的頁面上,與一般資料列的資料分開。 資料壓縮不適用於個別儲存的資料。
在 SQL Server 2005 (9.x) 中實作 Vardecimal 儲存格式的資料表會在升級時保留此設定。 您可以將資料列壓縮套用到具有 Vardecimal 儲存格式的資料表。 但是,由於資料列壓縮是 Vardecimal 儲存格式的超集,所以沒有理由保留 Vardecimal 儲存格式。 當您將 Vardecimal 儲存格式結合資料列壓縮時,十進位值不會取得額外的壓縮。 您可以將頁面壓縮套用到具有 Vardecimal 儲存格式的資料表;但是,Vardecimal 儲存格式資料行可能不會封存其他壓縮。
注意
所有 SQL Server 支援版本都支援 Vardecimal 儲存格式;但是,由於資料壓縮會達成相同的目標,所以 Vardecimal 儲存格式已遭到取代。 SQL Server 的未來版本將移除此功能。 請避免在新的開發工作中使用這項功能,並規劃修改目前使用這項功能的應用程式。
如需 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 資源來壓縮及擷取資料時,才使用封存壓縮。
封存壓縮的好處就是減少儲存體,這對於不常存取的資料很實用。 例如,如果您每個月的資料都有一個資料分割,而您的大多數活動發生在最近的月份,您可以封存較舊的月份來減少儲存需求。
下列系統檢視表包含叢集索引之資料壓縮的相關資訊:
type
和 type_desc
資料行包含 CLUSTERED COLUMNSTORE
和 NONCLUSTERED COLUMNSTORE
。data_compression
和 data_compression_desc
資料行包含 COLUMNSTORE
和 COLUMNSTORE_ARCHIVE
。sp_estimate_data_compression_savings (Transact-SQL) 程序也適用於資料行存放區索引。
當您搭配分割資料表和索引使用資料壓縮時,請注意以下考量事項:
當使用 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
的相同準則。
當卸除叢集索引時,除非修改了資料分割配置,否則對應的堆積資料分割會保留其資料壓縮設定。 如果資料分割配置有所變更,所有資料分割都會重建為未壓縮的狀態。 若要卸除叢集索引及變更資料分割配置,您需要執行以下步驟:
ALTER TABLE ... REBUILD
選項來修改資料表。卸除叢集索引 OFFLINE
是一項快速操作,因為只會移除叢集索引的上層。 卸除叢集索引 ONLINE
時,SQL Server 必須重建堆積兩次,一次在步驟 1,另一次在步驟 2。
當您搭配複寫使用資料壓縮時,請注意以下考量事項:
當快照集代理程式產生最初的結構描述指令碼時,新的結構描述會將相同的壓縮設定用於資料表和它的索引。 不能只在資料表上啟用壓縮,而不在索引上啟用壓縮。
如果是異動複寫,發行項結構描述選項會判斷哪些相依的物件和屬性必須編寫指令碼。 如需詳細資訊,請參閱 sp_addarticle。
散發代理程式在套用指令碼時,不會檢查是否有下層的訂閱者。 如果選取了壓縮的複寫,在下層訂閱者上建立資料表就會失敗。 如果是混合拓撲,請勿啟用壓縮的複寫。
如果是合併式複寫,發行集相容性層級會覆寫結構描述選項,並判斷所編寫指令碼的結構描述物件。
在混合拓撲的情況下,如果它不必支援新的壓縮選項,則發行集相容性層級應該設定為下層的訂閱者版本。 如果需要,請於建立資料表之後在訂閱者上壓縮資料表。
下表顯示在複寫期間控制壓縮的複寫設定。
使用者意圖 | 複寫資料表或索引的資料分割配置 | 複寫壓縮設定 | 指令碼行為 |
---|---|---|---|
複寫資料分割配置,以及在資料分割的訂閱者上啟用壓縮。 | True | True | 同時針對資料分割配置和壓縮設定編寫指令碼。 |
複寫資料分割配置,但是不壓縮訂閱者上的資料。 | 是 | 否 | 針對資料分割配置編寫指令碼,但是不針對資料分割的壓縮設定編寫指令碼。 |
不複寫資料分割配置,而且不壓縮訂閱者上的資料。 | False | False | 不針對資料分割或壓縮設定編寫指令碼。 |
如果所有資料分割都在發行者上壓縮,則壓縮訂閱者上的資料表,但是不複寫資料分割配置。 | False | True | 檢查所有資料分割是否啟用壓縮。 針對資料表層級上的壓縮編寫指令碼。 |
適用於:SQL Server
Azure SQL 資料庫
Azure SQL 受控執行個體
壓縮會發生在資料庫引擎中,而且資料會以非壓縮狀態呈現給 SQL Server 中的大多數其他元件。 這樣會將壓縮對其他元件的影響限制為以下因素:
事件
3月31日 下午11時 - 4月2日 下午11時
最大的 SQL、網狀架構和 Power BI 學習事件。 3 月 31 日 - 4 月 2 日。 使用程式代碼 FABINSIDER 來節省 $400 美元。
立即註冊訓練
模組
Maintain disks and volumes - Training
This module explores file system fragmentation and the tools that you can use to reduce fragmentation. Students will learn how Windows can compress files to take up less space on the hard disk.
文件
sp_estimate_data_compression_savings (Transact-SQL) - SQL Server
sp_estimate_data_compression_savings系統預存程式會傳回所要求物件的目前大小,並估計所要求壓縮狀態的物件大小。
了解如何使用 SQL Server Management Studio 或 Transact-SQL,在 SQL Server 中啟用資料表或索引的壓縮。
了解如何使用 SQL Server Management Studio 或 Transact-SQL,在 SQL Server 中停用資料表或索引的壓縮。