SQL Server 2008 Data Compression

Опубликовано: 16 Декабрь 2024
на канале: DBATAG

7,854

More Detailed about Data Compression can be seen at

http://sqlserver-training.com/data-co...

Follow us at
----------------------
Website : http://sqlserver-training.com/
Twitter :   / dbatag
Facebook :   / dbatag
Linked In :   / dbatag
Subscribe to Emails : http://feedburner.google.com/fb/a/mai...
================================

Data Compression feature of SQL Server 2008, can reduce the storage footprint of your databases. Often, data compression leads to a degradation in performance; however, SQL Server data compression simply stores data more efficiently in the rows and pages, which means that the in-memory storage when the data is in use is also reduced.

In SQL Server 2008, you can compress data for the following objects:

A table that is stored as a heap.
A table that is stored as a clustered index.
A nonclustered index.
An indexed view.
A partition in a partitioned table or index.
SQL Server supports two types of data compression:

Row compression and
page compression
Row Compression
Row compression is a more efficient version of data in-row storage. Conceptually, it is based on the SQL Server 2005 vardecimal storage format, which eliminates the
leading and trailing zeros in decimal values. Row compression in SQL Server 2008 is a whole new storage structure that reduces the size of the metadata that is required for variable-length columns. It uses no space to store zero and null values, which results in less space being required to store a row.

Page Compression
Page compression is a superset of (and implies) row compression. It also takes advantage of column prefix compression and page dictionary compression. In column prefix compression, if many columns start in the same way, the repeated information is replaced with a code. For example, if several product descriptions start with the word "blue...," blue could be replaced with "1." Page dictionary compression finds duplicate values on a page, stores the value in a dictionary, and replaces the duplicate values with a link to the item in the dictionary.

Implementing Data Compression
You can implement both types of data compression when you create a table or index, or by altering existing tables and indexes. You can do this by using Transact-SQL code or the Data Compression Wizard.