Data Compression in SQL Server 2008

In this article, I will explain about how to compress data in SQL Server.
  • 1466

Introduction

Data Compression is a new feature in Microsoft SQL Server 2008 which automatically compress data stored in database. It reduces the storage requirement for your data as well as it improves the IO performance. SQL Server provides two levels of compression which are explained below.

1. Row Level Compression:

  • It is the lower level compression which performs operations like changing the fixed character strings into variable length format
  • NULL and zero values across all data types are removed. And these values are not stored in the disk.
  • Stores CHAR data types as variable-length data types. For example, if you have CHAR (100) data type and you store 10 characters as data in it. Then only 10 characters space will be given to variable and blank characters will not stored.

Example

RowCompression-in-sql.jpg


execute_RowCompression-in-sql.jpg

2. Page Level Compression:

  • It is the lower level compression. It starts with row-level data compression. It uses two types of compression Prefix compression and Dictionary compression.
  • Prefix compression is used for common values pattern across all rows while Dictionary compression is used for exact values matches across a page.

Example

PageCompression-in-sql.jpg


execute_PageCompression-in-sql.jpg

Categories

More Articles

© 2013 dotNetheaven. All rights reserved.