Data compression features in SQL Server 2008

SQL Server SP2 brings us the vardecimal feature, which allows the originally fixed-length decimal data to be stored in a variable-length format in the data file, which is said to save 30% of the space for a typical data warehouse, while SQL Server 2008 On this basis, the data compression function is further enhanced. SQL Server 2008 now supports two options for row compression and page compression, and data compression options can be enabled on the following objects:

Table with no clustered index created

Table to create a clustered index

Nonclustered indexes (setting the compression option on a table does not affect nonclustered indexes on the table, so the compression of the clustered index needs to be set separately)

Indexed view

Individual partitions in partitioned tables and partitioned indexes

Why data compression is required

The first issue that might need to be discussed is why is Microsoft taking pains to implement and continually improve data compression in SQL Server today, as storage costs are declining?

Although storage costs are no longer the primary consideration in the traditional sense, this does not mean that database size is not a problem, since database dimensions are significantly associated with management costs and performance issues, in addition to the storage costs.

First, let's discuss why there is a problem with managing costs. Because the database needs to be backed up, the larger the size of the database, the longer the backup time will be, and, of course, the additional cost of the backup hardware that is consumed ( Including the cost of backup media required and the cost of purchasing for a more advanced backup device to meet backup windows, there is a management cost that is the cost of maintaining the database, such as the DBCC tasks we often need to complete, and the larger the database size, the more time we need to complete these tasks.

Then we'll look at the performance problem. SQL Server reads data on a data page when it scans the disk, so if the more rows of data are contained in a single data page, the more data the SQL Server obtains in the data page IO, the higher the performance.

Finally, considering the cost of storage, as in the case of vardecimal compressed data from the original SQL Server SP2, 30% of space savings means 30% storage costs, and according to the test data currently released by SQL Server 2008, The use of new data compression technology to achieve 2x-7x storage rate, plus if the enterprise to consider the disaster and increased storage space, so the cost of storage hardware savings will be significant.

How to use data compression

The compression options in SQL Server 2008 can be set by option when a table or index is created, for example:CREATE TABLE TestTable (col1 int, col2 varchar(200)) WITH (DATA_COMPRESSION = ROW);

If you need to change the compression options for a partition, you can use the following statement:ALTER TABLE TestTable REBUILD PARTITION = 1 WITH (DATA COMPRESSION = PAGE);

If you need to set different compression options for each partition of the partition table, you can use the following statement: (SQL Server 2008 can use different compression options for different partitions, which is important for data warehousing applications because the fact tables of the data warehouse usually have one or several hot partitions, The data in these partitions is often updated, and the compression options can be turned off to prevent data compression from causing additional processing load to the data updates on these partitions.CREATE TABLE PartitionedTable (col1 int, col2 varchar(200))
ON PS1 (col1)
WITH (
DATA_COMPRESSION = ROW ON PARTITIONS(1),
DATA_COMPRESSION = PAGE ON PARTITION(2 TO 4));

If you are setting compression options for an index, you can use:

CREATE INDEX IX_TestTable_Col1 ON TestTable (Col1) WITH (DATA_COMPRESSION = ROW);

If you are modifying the compression options for an index, you can use:ALTER INDEX IX_TestTable_Col1 ON TestTable REBUILD WITH (DATA_COMPRESSION = ROW);

SQL Server 2008 also provides an object size named Sp_estimate_data_compression_savings stored procedure to help DBA estimate the activation compression option.