資料壓縮Data Compression

適用於: 是SQL Server 是Azure SQL Database 否Azure SQL 資料倉儲 否平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

SQL Server 2017SQL Server 2017 Azure SQL DatabaseAzure SQL Database 支援資料列和頁面壓縮 (針對資料列存放區的資料表和索引),亦支援資料行存放區和資料行存放區封存壓縮 (針對資料行存放區的資料表和索引)。and Azure SQL DatabaseAzure SQL Database support row and page compression for rowstore tables and indexes, and supports columnstore and columnstore archival compression for columnstore tables and indexes.

如果是資料列存放區資料表和索引,使用資料壓縮功能有助於減少資料庫的大小。For rowstore tables and indexes, use the data compression feature to help reduce the size of the database. 除了節省空間之外,資料壓縮也有助於改善 I/O 密集型工作負載的效能,因為資料會儲存在更少的頁面中,而且查詢需要從磁碟讀取的頁面也變少了。In addition to saving space, data compression can help improve performance of I/O intensive workloads because the data is stored in fewer pages and queries need to read fewer pages from disk. 但是在與應用程式交換資料時,資料庫伺服器上需要額外的 CPU 資源來壓縮和解壓縮資料。However, extra CPU resources are required on the database server to compress and decompress the data, while data is exchanged with the application. 您可以針對下列資料庫物件來設定資料列和頁面壓縮:You can configure row and page compression on the following database objects:

  • 儲存為堆積的整個資料表。A whole table that is stored as a heap.
  • 儲存為叢集索引的整個資料表。A whole table that is stored as a clustered index.
  • 整個非叢集索引。A whole nonclustered index.
  • 整個索引檢視。A whole indexed view.
  • 如果是分割資料表和索引,您可以針對每個資料分割設定壓縮選項,而物件的不同資料分割則不必擁有相同的壓縮設定。For partitioned tables and indexes, you can configure the compression option for each partition, and the various partitions of an object do not have to have the same compression setting.

如果是資料行存放區資料表和索引,所有資料行存放區資料表和索引一律都使用資料行存放區壓縮,而且使用者無法進行設定。For columnstore tables and indexes, all columnstore tables and indexes always use columnstore compression and this is not user configurable. 當您可負擔額外的時間和 CPU 資源來儲存及擷取資料時,使用資料行存放區封存壓縮會進一步減少資料大小。Use columnstore archival compression to further reduce the data size for situations when you can afford extra time and CPU resources to store and retrieve the data. 您可以針對下列資料庫物件來設定資料行存放區封存壓縮:You can configure columnstore archival compression on the following database objects:

  • 整個資料行存放區資料表或整個叢集資料行存放區索引。A whole columnstore table or a whole clustered columnstore index. 因為資料行存放區資料表會儲存成叢集資料行存放區索引,所以兩種方法有相同的結果。Since a columnstore table is stored as a clustered columnstore index, both approaches have the same results.
  • 整個非叢集資料行存放區索引。A whole nonclustered columnstore index.
  • 如果是分割資料行存放區資料表和資料行存放區索引,您可以針對每個資料分割設定封存壓縮選項,而不同資料分割則不必擁有相同的封存壓縮設定。For partitioned columnstore tables and columnstore indexes, you can configure the archival compression option for each partition, and the various partitions do not have to have the same archival compression setting.

注意

資料也可以使用 GZIP 演算法格式進行壓縮。Data can also be compressed using the GZIP algorithm format. 這是額外的步驟,最適合在封存舊資料進行長期儲存時壓縮部分資料。This is an additional step and is most suitable for compressing portions of the data when archiving old data for long-term storage. 使用 COMPRESS 函數壓縮的資料無法編製索引。Data compressed using the COMPRESS function cannot be indexed. 如需詳細資訊,請參閱 COMPRESS (Transact-SQL)For more information, see COMPRESS (Transact-SQL).

使用資料列和頁面壓縮時的考量Considerations for When You Use Row and Page Compression

當您使用資料列和頁面壓縮時,請注意以下考量事項:When you use row and page compression, be aware the following considerations:

  • Service Pack 或後續版本中的資料壓縮詳細資料可能會變更,恕不另行通知。The details of data compression are subject to change without notice in service packs or subsequent releases.

  • 壓縮適用於 Azure SQL DatabaseAzure SQL DatabaseCompression is available in Azure SQL DatabaseAzure SQL Database

  • 每一個 SQL ServerSQL Server版本中都無法使用壓縮。Compression is not available in every edition of SQL ServerSQL Server. 如需詳細資訊,請參閱 SQL Server 2016 版本支援的功能For more information, see Features Supported by the Editions of SQL Server 2016.

  • 壓縮不適用於系統資料表。Compression is not available for system tables.

  • 壓縮可讓更多的資料列儲存在頁面上,但是不會變更資料表或索引的資料列大小上限。Compression can allow more rows to be stored on a page, but does not change the maximum row size of a table or index.

  • 當資料列大小上限加上壓縮負擔超過 8060 個位元組的資料列大小上限時,資料表將無法啟用壓縮。A table cannot be enabled for compression when the maximum row size plus the compression overhead exceeds the maximum row size of 8060 bytes. 例如,因為有額外的壓縮負荷,所以無法壓縮具有資料行 c1char(8000) 及 c2char(53) 的資料表。For example, a table that has the columns c1char(8000) and c2char(53) cannot be compressed because of the additional compression overhead. 當使用 Vardecimal 儲存格式時,將會在啟用此格式時執行資料列大小檢查。When the vardecimal storage format is used, the row-size check is performed when the format is enabled. 對於資料列和頁面壓縮而言,最初壓縮物件時會執行資料列大小檢查,然後在插入或修改每一個資料列時加以檢查。For row and page compression, the row-size check is performed when the object is initially compressed, and then checked as each row is inserted or modified. 壓縮會強制執行下列兩個規則:Compression enforces the following two rules:

    • 固定長度類型的更新一定要成功。An update to a fixed-length type must always succeed.
    • 停用資料壓縮一定要成功。Disabling data compression must always succeed. 即使壓縮的資料列適合頁面大小,這表示它小於 8060 個位元組;如果它未壓縮, SQL ServerSQL Server 會防止不適合資料列大小的更新。Even if the compressed row fits on the page, which means that it is less than 8060 bytes; SQL ServerSQL Server prevents updates that would not fit on the row when it is uncompressed.
  • 當指定了資料分割清單時,壓縮類型可以在個別資料分割上設定為 ROW、PAGE 或 NONE。When a list of partitions is specified, the compression type can be set to ROW, PAGE, or NONE on individual partitions. 如果未指定資料分割的清單,將會設定所有資料分割,並包含陳述式中所指定的資料壓縮屬性。If the list of partitions is not specified, all partitions are set with the data compression property that is specified in the statement. 在建立資料表或索引時,除非另外指定,否則資料壓縮會設定為 NONE。When a table or index is created, data compression is set to NONE unless otherwise specified. 在修改資料表時,除非另外指定,否則會保留現有的壓縮。When a table is modified, the existing compression is preserved unless otherwise specified.

  • 如果您指定資料分割清單或超出範圍的資料分割,則會產生錯誤。If you specify a list of partitions or a partition that is out of range, an error is generated.

  • 非叢集索引不會繼承資料表的壓縮屬性。Nonclustered indexes do not inherit the compression property of the table. 若要壓縮索引,您必須明確設定索引的壓縮屬性。To compress indexes, you must explicitly set the compression property of the indexes. 根據預設,當建立索引時,索引的壓縮設定會設定為 NONE。By default, the compression setting for indexes is set to NONE when the index is created.

  • 在堆積上建立叢集索引時,此叢集索引會繼承堆積的壓縮狀態,除非指定了替代的壓縮狀態。When a clustered index is created on a heap, the clustered index inherits the compression state of the heap unless an alternative compression state is specified.

  • 當堆積設定了頁面層級壓縮時,頁面只會以下列方式接收頁面層級壓縮:When a heap is configured for page-level compression, pages receive page-level compression only in the following ways:

    • 資料會在啟用大量最佳化的情況下大量匯入。Data is bulk imported with bulk optimizations enabled.
    • 使用 INSERT INTO ...WITH (TABLOCK) 語法與資料表沒有非叢集索引。Data is inserted using INSERT INTO ... WITH (TABLOCK) syntax and the table does not have a nonclustered index.
    • 執行 ALTER TABLE ...REBUILD 陳述式並指定 PAGE 壓縮選項來重建資料表。A table is rebuilt by executing the ALTER TABLE ... REBUILD statement with the PAGE compression option.
  • 重建堆積之前,配置在堆積中成為 DML 作業一部分的新頁面不會使用 PAGE 壓縮。New pages allocated in a heap as part of DML operations do not use PAGE compression until the heap is rebuilt. 您可以透過移除並重新套用壓縮,或建立並移除叢集索引,重建堆積。Rebuild the heap by removing and reapplying compression, or by creating and removing a clustered index.

  • 變更堆積的壓縮設定需要重建資料表上的所有非叢集索引,好讓它們擁有指向堆積內新資料列位置的指標。Changing the compression setting of a heap requires all nonclustered indexes on the table to be rebuilt so that they have pointers to the new row locations in the heap.

  • 您可以在線上或離線時啟用或停用 ROW 或 PAGE 壓縮。You can enable or disable ROW or PAGE compression online or offline. 在堆積上啟用壓縮對於線上作業而言是單一執行緒的作業。Enabling compression on a heap is single threaded for an online operation.

  • 啟用或停用資料列或頁面壓縮的磁碟空間需求與建立或重建索引的需求相同。The disk space requirements for enabling or disabling row or page compression are the same as for creating or rebuilding an index. 對於分割的資料而言,您可以一次啟用或停用一個資料分割的壓縮來減少所需的空間。For partitioned data, you can reduce the space that is required by enabling or disabling compression for one partition at a time.

  • 若要決定資料分割資料表中資料分割的壓縮狀態,請查詢 sys.partitions 目錄檢視的 data_compression 資料行。To determine the compression state of partitions in a partitioned table, query the data_compression column of the sys.partitions catalog view.

  • 當您壓縮索引時,可以在壓縮資料列和頁面的情況下壓縮分葉層級頁面。When you are compressing indexes, leaf-level pages can be compressed with both row and page compression. 非分葉層級頁面不會收到頁面壓縮。Non-leaf-level pages do not receive page compression.

  • 由於大數值資料類型的大小之緣故,這些類型有時會單獨儲存在特殊用途的頁面上,與一般資料列的資料分開。Because of their size, large-value data types are sometimes stored separately from the normal row data on special purpose pages. 資料壓縮不適用於個別儲存的資料。Data compression is not available for the data that is stored separately.

  • SQL Server 2005 (9.x)SQL Server 2005 (9.x) 中實作 Vardecimal 儲存格式的資料表會在升級時保留此設定。Tables that implemented the vardecimal storage format in SQL Server 2005 (9.x)SQL Server 2005 (9.x), retain that setting when upgraded. 您可以將資料列壓縮套用到具有 Vardecimal 儲存格式的資料表。You can apply row compression to a table that has the vardecimal storage format. 但是,由於資料列壓縮是 Vardecimal 儲存格式的超集,所以沒有理由保留 Vardecimal 儲存格式。However, because row compression is a superset of the vardecimal storage format, there is no reason to retain the vardecimal storage format. 當您將 Vardecimal 儲存格式結合資料列壓縮時,十進位值不會取得額外的壓縮。Decimal values gain no additional compression when you combine the vardecimal storage format with row compression. 您可以將頁面壓縮套用到具有 Vardecimal 儲存格式的資料表;但是,Vardecimal 儲存格式資料行可能不會封存其他壓縮。You can apply page compression to a table that has the vardecimal storage format; however, the vardecimal storage format columns probably will not achieve additional compression.

    注意

    SQL Server 2017SQL Server 2017 支援 Vardecimal 儲存格式;但是,由於資料列層級的壓縮會達成相同的目標,所以 Vardecimal 儲存格式已被取代。supports the vardecimal storage format; however, because row-level compression achieves the same goals, the vardecimal storage format is deprecated. 這項功能處於維護模式,並可能在 Microsoft SQL Server 的未來版本中移除。This feature is in maintenance mode and may be removed in a future version of Microsoft SQL Server. 請避免在新的開發工作中使用這項功能,並規劃修改目前使用這項功能的應用程式。Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

使用資料行存放區和資料行存放區封存壓縮Using Columnstore and Columnstore Archive Compression

適用於SQL ServerSQL Server (SQL Server 2014 (12.x)SQL Server 2014 (12.x)目前版本)、Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server ( SQL Server 2014 (12.x)SQL Server 2014 (12.x) through current version), Azure SQL DatabaseAzure SQL Database.

基本概念Basics

資料行存放區資料表和索引永遠都會以資料行存放區壓縮形式來儲存。Columnstore tables and indexes are always stored with columnstore compression. 您可以進一步減少資料行存放區資料的大小,只要設定稱為封存壓縮的額外壓縮即可。You can further reduce the size of columnstore data by configuring an additional compression called archival compression. 為了執行封存壓縮, SQL ServerSQL Server 會針對資料執行 Microsoft XPRESS 壓縮演算法。To perform archival compression, SQL ServerSQL Server runs the Microsoft XPRESS compression algorithm on the data. 您可以使用下列資料壓縮類型來新增或移除封存壓縮:Add or remove archival compression by using the following data compression types:

  • 使用 COLUMNSTORE_ARCHIVE 資料壓縮,以封存壓縮來壓縮資料行存放區的資料。Use COLUMNSTORE_ARCHIVE data compression to compress columnstore data with archival compression.
  • 使用 COLUMNSTORE 資料壓縮,將封存壓縮解壓縮。Use COLUMNSTORE data compression to decompress archival compression. 產生的資料會持續以資料行存放區壓縮形式壓縮。The resulting data continue to be compressed with columnstore compression.

若要新增封存壓縮,請使用 ALTER TABLE (Transact-SQL)ALTER INDEX (Transact-SQL) 搭配 REBUILD 選項和 DATA COMPRESSION = COLUMNSTORE_ARCHIVE。To add archival compression, use ALTER TABLE (Transact-SQL) or ALTER INDEX (Transact-SQL) with the REBUILD option and DATA COMPRESSION = COLUMNSTORE_ARCHIVE.

範例:Examples:

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。To remove archival compression and restore the data to columnstore compression, use ALTER TABLE (Transact-SQL) or ALTER INDEX (Transact-SQL) with the REBUILD option and DATA COMPRESSION = COLUMNSTORE.

範例:Examples:

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) ) ;  

下一個範例會在某些資料分割上將資料壓縮設定為資料行存放區,以及在其他資料分割上設定為資料行存放區封存。This next example sets the data compression to columnstore on some partitions, and to columnstore archival on other partitions.

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

效能Performance

以封存壓縮壓縮資料行存放區索引會造成該索引的效能比沒有封存壓縮的資料行存放區索引還要慢。Compressing columnstore indexes with archival compression, causes the index to perform slower than columnstore indexes that do not have the archival compression. 只有當您可以負擔使用額外時間和 CPU 資源來壓縮及擷取資料時,才使用封存壓縮。Use archival compression only when you can afford to use extra time and CPU resources to compress and retrieve the data.

封存壓縮的好處就是減少儲存體,這對於不常存取的資料很實用。The benefit of archival compression, is reduced storage, which is useful for data that is not accessed frequently. 例如,如果您每個月的資料都有一個資料分割,而您的大多數活動發生在最近的月份,您可以封存較舊的月份來減少儲存需求。For example, if you have a partition for each month of data, and most of your activity is for the most recent months, you could archive older months to reduce the storage requirements.

中繼資料Metadata

下列系統檢視表包含叢集索引之資料壓縮的相關資訊:The following system views contain information about data compression for clustered indexes:

sp_estimate_data_compression_savings (Transact-SQL) 程序也適用於資料行存放區索引。The procedure sp_estimate_data_compression_savings (Transact-SQL) can also apply to columnstore indexes.

壓縮對分割資料表和索引有何影響How Compression Affects Partitioned Tables and Indexes

當您搭配分割資料表和索引使用資料壓縮時,請注意以下考量事項:When you use data compression with partitioned tables and indexes, be aware of the following considerations:

  • 當使用 ALTER PARTITION 陳述式分割資料分割時,兩個資料分割都會繼承原始資料分割的資料壓縮屬性。When partitions are split by using the ALTER PARTITION statement, both partitions inherit the data compression attribute of the original partition.

  • 當合併兩個資料分割時,所產生的資料分割會繼承目標資料分割的資料壓縮屬性。When two partitions are merged, the resultant partition inherits the data compression attribute of the destination partition.

  • 若要切換資料分割,此資料分割的資料壓縮屬性必須符合資料表的壓縮屬性。To switch a partition, the data compression property of the partition must match the compression property of the table.

  • 您可以使用兩種語法變化來修改分割資料表或索引的壓縮:There are two syntax variations that you can use to modify the compression of a partitioned table or index:

    • 下列語法只會重建參考的資料分割:The following syntax rebuilds only the referenced partition:
      ALTER TABLE <table_name>   
      REBUILD PARTITION = 1 WITH (DATA_COMPRESSION =  <option>)  
      
    • 下列語法會將現有的壓縮設定用於任何未參考的資料分割,藉以重建整個資料表:The following syntax rebuilds the whole table by using the existing compression setting for any partitions that are not referenced:
      ALTER TABLE <table_name>   
      REBUILD PARTITION = ALL   
      WITH (DATA_COMPRESSION = PAGE ON PARTITIONS(<range>),  
      ... )  
      

    分割索引會遵循使用 ALTER INDEX 的相同原則。Partitioned indexes follow the same principle using ALTER INDEX.

  • 當卸除叢集索引時,除非修改了資料分割配置,否則對應的堆積資料分割會保留其資料壓縮設定。When a clustered index is dropped, the corresponding heap partitions retain their data compression setting unless the partitioning scheme is modified. 如果資料分割配置有所變更,所有資料分割都會重建為未壓縮的狀態。If the partitioning scheme is changed, all partitions are rebuilt to an uncompressed state. 若要卸除叢集索引及變更資料分割配置,您需要執行以下步驟:To drop a clustered index and change the partitioning scheme requires the following steps:

    1. 卸除叢集索引。Drop the clustered index.
    2. 使用指定壓縮選項的 ALTER TABLE ...REBUILD ... 選項來修改資料表。Modify the table by using the ALTER TABLE ... REBUILD ... option that specifies the compression option.

    在線上卸除叢集索引將會是非常快速的作業,因為只會移除叢集索引的上層。To drop a clustered index OFFLINE is a very fast operation, because only the upper levels of clustered indexes are removed. 在線上卸除叢集索引時, SQL ServerSQL Server 必須重建堆積兩次,一次在步驟 1,另一次在步驟 2。When a clustered index is dropped ONLINE, SQL ServerSQL Server must rebuild the heap two times, once for step 1 and once for step 2.

壓縮將如何影響複寫How Compression Affects Replication

適用於SQL ServerSQL Server (SQL Server 2014 (12.x)SQL Server 2014 (12.x)目前版本)。Applies to: SQL ServerSQL Server ( SQL Server 2014 (12.x)SQL Server 2014 (12.x) through current version).
當您搭配複寫使用資料壓縮時,請注意以下考量事項:When you are using data compression with replication, be aware of the following considerations:

  • 當快照集代理程式產生最初的結構描述指令碼時,新的結構描述會將相同的壓縮設定用於資料表和它的索引。When the Snapshot Agent generates the initial schema script, the new schema uses the same compression settings for both the table and its indexes. 不能只在資料表上啟用壓縮,而不在索引上啟用壓縮。Compression cannot be enabled on just the table and not the index.
  • 如果是異動複寫,發行項結構描述選項會判斷哪些相依的物件和屬性必須編寫指令碼。For transactional replication the article schema option determines what dependent objects and properties have to be scripted. 如需詳細資訊,請參閱 sp_addarticleFor more information, see sp_addarticle.
    散發代理程式在套用指令碼時,不會檢查是否有下層的訂閱者。The Distribution Agent does not check for down-level Subscribers when it applies scripts. 如果選取了壓縮的複寫,在下層訂閱者上建立資料表就會失敗。If the replication of compression is selected, creating the table on down-level Subscribers fails. 如果是混合拓撲,請勿啟用壓縮的複寫。In the case of a mixed topology, do not enable the replication of compression.
  • 如果是合併式複寫,發行集相容性層級會覆寫結構描述選項,並判斷所編寫指令碼的結構描述物件。For merge replication, publication compatibility level overrides the schema options and determines the schema objects that are scripted.
    在混合拓撲的情況下,如果它不必支援新的壓縮選項,則發行集相容性層級應該設定為下層的訂閱者版本。In the case of a mixed topology, if it is not required to support the new compression options, the publication compatibility level should be set to the down-level Subscriber version. 如果需要的話,請於建立資料表之後在訂閱者上壓縮資料表。If it is required, compress tables on the Subscriber after they have been created.

下表顯示在複寫期間控制壓縮的複寫設定。The following table shows replication settings that control compression during replication.

使用者意圖User intent 複寫資料表或索引的資料分割配置Replicate partition scheme for a table or index 複寫壓縮設定Replicate compression settings 指令碼行為Scripting behavior
複寫資料分割配置,以及在資料分割的訂閱者上啟用壓縮。To replicate the partition scheme and enable compression on the Subscriber on the partition. TrueTrue TrueTrue 同時針對資料分割配置和壓縮設定編寫指令碼。Scripts both the partition scheme and the compression settings.
複寫資料分割配置,但是不壓縮訂閱者上的資料。To replicate the partition scheme but not compress the data on the Subscriber. TrueTrue FalseFalse 針對資料分割配置編寫指令碼,但是不針對資料分割的壓縮設定編寫指令碼。Scripts out the partition scheme but not the compression settings for the partition.
不複寫資料分割配置,而且不壓縮訂閱者上的資料。To not replicate the partition scheme and not compress the data on the Subscriber. FalseFalse FalseFalse 不針對資料分割或壓縮設定編寫指令碼。Does not script partition or compression settings.
如果所有資料分割都在發行者上壓縮,則壓縮訂閱者上的資料表,但是不複寫資料分割配置。To compress the table on the Subscriber if all the partitions are compressed on the Publisher, but not replicate the partition scheme. FalseFalse TrueTrue 檢查所有資料分割是否啟用壓縮。Checks if all the partitions are enabled for compression.

針對資料表層級上的壓縮編寫指令碼。Scripts out compression at the table level.

壓縮對於其他 SQL Server 元件有何影響How Compression Affects Other SQL Server Components

適用於SQL ServerSQL Server (SQL Server 2014 (12.x)SQL Server 2014 (12.x)目前版本)。Applies to: SQL ServerSQL Server ( SQL Server 2014 (12.x)SQL Server 2014 (12.x) through current version).

壓縮會發生在儲存引擎中,而且資料會以非壓縮狀態呈現給 SQL ServerSQL Server 中的大多數其他元件。Compression occurs in the storage engine and the data is presented to most of the other components of SQL ServerSQL Server in an uncompressed state. 這樣會將壓縮對其他元件的影響限制為以下情況:This limits the effects of compression on the other components to the following:

  • 大量匯入及匯出作業Bulk import and export operations
    當匯出資料時 (即使是原生格式),資料為非壓縮資料列格式的輸出。When data is exported, even in native format, the data is output in the uncompressed row format. 這可能會造成匯出的資料檔大小比來源資料大出許多。This can cause the size of exported data file to be significantly larger than the source data.
    當匯入資料時,如果目標資料表已啟用壓縮,則儲存引擎會將資料轉換成壓縮的資料列格式。When data is imported, if the target table has been enabled for compression, the data is converted by the storage engine into compressed row format. 這樣可能會造成 CPU 使用量增加 (相較於資料匯入未壓縮的資料表時)。This can cause increased CPU usage compared to when data is imported into an uncompressed table.
    將資料大量匯入具有頁面壓縮的堆積內時,大量匯入作業會在插入具有頁面壓縮的資料時,嘗試壓縮這些資料。When data is bulk imported into a heap with page compression, the bulk import operation tries to compress the data with page compression when the data is inserted.
  • 壓縮不會影響備份和還原。Compression does not affect backup and restore.
  • 壓縮不會影響記錄傳送。Compression does not affect log shipping.
  • 資料壓縮與疏鬆資料行不相容。Data compression is incompatible with sparse columns. 因此,包含疏鬆資料行的資料表無法加以壓縮,也無法將疏鬆資料行加入至壓縮的資料表。Therefore, tables containing sparse columns cannot be compressed nor can sparse columns be added to a compressed table.
  • 啟用壓縮可能會造成查詢計畫變更,因為系統會使用不同的頁數以及每頁不同的資料列數來儲存資料。Enabling compression can cause query plans to change because the data is stored using a different number of pages and number of rows per page.

另請參閱See Also

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