Data Compression in SQL Server

To enable a compression type of an existing table must use the ALTER TABLE command with the REBUILD option as follows for example

 ALTER TABLE Sales.SalesOrderDetail

 





 REBUILD WITH (DATA_COMPRESSION = ROW)

This script enables row-level compression on Sales.SalesOrderDetail table. To enable compression on a table you can also go in the Data Compression Wizard after calculating the estimates. For if we go from page Select Compression Type Next we access the page by clicking the Select an Output Option, where you can choose whether to create a script, perform the operation immediately or scheduled as a job

Having these tools available to do the first step is to decide which objects to apply these features. SQL Server is very flexible from this point of view because it allows you to use a mix of different techniques on different database objects. For example, you can apply row-level compression to some tables, the page-level to other tables and do not apply either to the rest of the tables, compression, or apply only to some indexes, and so on. The two factors that should guide the choice of which objects to apply compression are essentially two: estimation of the space saving and loading the application.

Regarding the first point should be noted that the amount of space saved by compressing a table depends on the data it contains. In fact, some data is compressed significantly while others do not.

The tables which should be applied compression should have the following characteristics:

• Columns with numeric data types or fixed length in which many values ​​do not require all the bytes allocated (eg integer values ​​with low values)
• Columns in many rows contain the NULL value
• Significant amounts of data or repeated repeated prefixes

The tables that you do not enjoy many of the compression are those with the following features:

• Columns with fixed-length data types where most of the values ​​requires all bytes allocated for that specific type of data
• Data not repeated very
• Repeated data with prefixes non-recurring
• FILESTREAM data type

With regard to the application load must be said that the pages are compressed tablets, even when they are read into memory. The data concerned (and not the entire page) is decompressed only if they serve for a sort operation, the join, as part of the filter response to a query, or they are updated by an application. So there is a decompressed in memory of the compressed page.

Unzip the data requires a load to the CPU and the load must be carefully evaluated (even if the other side compression reduces the number of input and output). The CPU load of the compression at the page level may be greater than the row level. To make the right choices regarding the use or not compression should bear in mind all these aspects.

• <<
• 1
• 2
• 3
• >>