MSSQL2008 Data compression method

The data compression feature allows Sol Server 2008 to perform data compression in tables, indexes, and partitions, which not only saves disk space, but also allows more data to be put into RAM, improving the performance of database queries.

1. Enable row compression

If we want to enable row compression on the specified table, you can execute the following statement:

CREATE TABLE Alpha (col1 int,col2 char (1000))

With (data_compression = ROW)

2. Enable page compression

If we want to enable compression on the specified partition, the following statement can be executed:

CREATE TABLE alpha_partition (col1 int,col2 char (1000))

On Partitionscheme (Col 1)

With

(data_compression = PAGE on)

Partitions (1-3)

3, modify the compression

If you want to modify an existing index to use compression, you can execute the following statement:

ALTER INDEX collndx on Alpha REBUILD

With (Data_c0mpression=page)

ALTER INDEX collndx on Alpha REBUILD

Partition = 2

With (Data_compression=page)

After you perform data compression, use the new SQL Server 2008 stored procedure "Sp_estimate_data_compression_savings" to evaluate how much resources you can save after applying data compression. (The space-saving size depends on the data type) performing "data compression" can greatly improve query performance, because data compression saves resources that allow more data to be loaded into RAM, which is necessary to improve the performance of queries. AC qq:2881064157

MSSQL2008 Data compression method