Table Data compression

For row-stored tables and indexes, using data compression can help reduce the size of the database. In addition to saving space, data compression can also help improve the performance of I/O-intensive workloads because the data is stored in fewer pages and queries require fewer pages to read from disk. However, when exchanging data with an application, additional CPU resources are required on the database server to compress and decompress the data.

1, View the table's storage properties on the Storage tab of the table

2,sp_estimate_data_compression_savings Estimating the effect of compression

Use Sp_estimate_data_compression_savings to estimate the amount of savings that can be achieved when row compression or page compression is enabled on a table or partition.

EXECsys.sp_estimate_data_compression_savings@SCHEMA_NAME='dbo',       @OBJECT_NAME='table_name',       @INDEX_ID=NULL,       @PARTITION_NUMBER=NULL,       @DATA_COMPRESSION='ROW'

3, compression has additional overhead

You cannot enable compression on a table when the maximum row size plus the compression overhead exceeds the maximum row size of 8,060 bytes. For example, you cannot compress a table with C1 char (8000) and C2 char (53) columns because of the additional compression overhead

Implementation of 4,row compression

Enabling row compression only changes the physical storage format of the data associated with the data type, without changing its syntax or semantics. When you enable compression on one or more tables, you do not need to change the application. The new record storage format has the following major changes:

• Reduces the metadata overhead associated with the record. This metadata is information about the column, column length, and offset. In some cases, the metadata overhead may be greater than the old storage format.
• It uses variable-length storage formats for numeric types (for example, Integer, Decimal, and float) and numeric-based types (for example, DateTime and money).
• It stores fixed-length strings by using variable-size formats that do not store null characters.

Note: NULL and 0 values for all data types are optimized so that they do not occupy any bytes.

The principle of implementation, the popular explanation is the purpose of compressing the data by modifying the physical storage format, but the modification of the physical storage format does not affect the syntax used by the field, for example:

• For numeric types and numeric-based types, the length of the data type is certain, such as bigint occupies 8 bytes, but for the value of one, only a single byte can be stored, enabling row compression can save 7 bytes of space;
• For fixed-length string types, if the stored data does not reach the specified length, it fills with empty characters, such as the type char (200) used to hold the string "1" will take 200 bytes, but when row compression is enabled, the empty character of the padding is removed and only 1 bytes are stored.
• For the type bit, additional 4 bits are required to hold the metadata in addition to the space consumed by itself, so you can also benefit from row compression.

Implementation of 5,page compression

Page compression contains row compression,As well as prefix compression and dictionary compression,When page compression is used, only row compression is used to compress the non-leaf-level pages of the index .

6. Enable compression on the table via SSMs

Manage Compression,select Compression type with table storage

7, write script to turn on the table compression function

The amount of compressed data can be pre-evaluated and then compressed

ExecSp_estimate_data_compression_savings@schema_name=  dbo ' 

[dbo]. [hierarchyall= ROW]     

8. Turn on page compression on index

SELECTname, index_id fromsys.indexesWHERE object_name(object_id)=N'TransactionHistory';EXECsp_estimate_data_compression_savings@schema_name = 'Production',     @object_name = 'TransactionHistory',    @index_id = 2,     @partition_number = NULL,     @data_compression = 'PAGE' ; ALTER INDEXIx_transactionhistory_productid onProduction.transactionhistory
REBUILD PARTITION=  All 
 with(data_compression=PAGE);GO

Table Data compression