Data Compression in SQL Server
Page 1 of 3
Data compression (data compression) in SQL Server introduced with service pack 2 version 2005, when it was proposed a new mechanism for storing data that is numeric and decimal format varDecimal. This format allows numeric and decimal data types to be stored in a column of variable size. This concept has been extended with later versions of SQL Server to other data types such as int, char and float.
Data compression reduces storage costs of course and improves query performance by reducing especially the input and output. SQL Server supports row-level compression and page level, both in tables and indexes:
Compression at the row level (row compression) - This type of compression is based on the variability of the structures of the different types of data and to store in a column only the necessary data. In practice, fixed-length data types are stored in a variable-length format. Consider as an example a column of type CHAR (100) stored in a variable-length format. In this case, if you store the word 'hello' are saved only four characters instead of 100, a savings of 96% of memory compared to a fixed-length storage format. This type of compression is an extension of the storage format varDecimal that, as already mentioned, was introduced in SQL Server 2005. Another thing to note in this context is that this type of compression does not require disk space to zero or NULL values.
Page-level compression (compression page) - This type of compression acts on redundant data that may be present in one or more lines of a given page, even using prefixes and compression dictionary. For example, if a table is partitioned using a column prefix, all data of the same partition have the same prefix. In this case, if the prefix of the values ​​of such a page begins with 'AAA100BB-' (could be the code of a product), the database engine stores this code only once and then the page references in all places where it operates.
Although it is thus evident that the data compression can reduce the size of our databases or indexes, it is always best to first evaluate the estimation of the space that you save in a table or index using the system procedure sp_estimate_data_compression_savings or through the Data Compression Wizard .
Another interesting aspect concerns the knowledge of the level of fragmentation of existing data might suggest that in some cases to reconstruct the indices rather than using compression.
 |
MS Access Course
Learn how to create and manage databases easily and quickly. Discount -10% until 06/01/2012. |
 |
Course MySQL
Management of open-source database. -15% Discount until 06/01/2012. |
 |
Course Database and SQL
Creating and managing relational databases. -15% Discount until 06/01/2012. |
