There are two types of data compression in SQL Server.

  • ROW Compression. This compression feature takes into account the variable data type structures defining a column. Take, for instance, a CHAR(100) column stored in a variable length storage format will only use up the amount of storage defined by the data. Storing “SQL Server 2008 ” in the column will only require storing fifteen characters instead of the full 100 characters, thereby, a 85% savings on storage space. This is the extension of the vardecimal storage format made available in SQL Server 2005 Service Pack 2. Note, also, that this compression feature does not take any disk space for zero or null values.
  • PAGE Compression. This compression feature is a superset of ROW compression and takes into account the redundant data in one or more rows on a given page. It also uses prefix and dictionary compression. What this simply means is that for both page compression techniques, the storage engine cuts down on repeated data in the page. For example, if a table is partitioned using a column prefix, all data in a specific partition will have the same or similar prefix. Let’s say the value of columns start with something like A1000Q-xxxx like some product codes, the storage engine store the A1000Q- once on the page and then refer to this value from all other occurrences of this value on the same page. This can also be said of a column with a defined DEFAULT constraint. Page compression only occurs when the page is full to optimize the performance.

Steps:

Right click on the Table ==> Properties==> Storage==> Manage Compression

This will launch the Data Compression Wizard. On the Welcome to Data Compression Wizard, click Next.

On the Select Compression Type page, click the drop-down menu on the Compression Type column to select your compression of choice.

Click the Calculate button to display a similar information provided by the table/stored procedure.

Notes:

1.Data Compression in SQL Server has been made available in SQL Server 2005
with Service Pack 2.
2.SQL Server 2008 supports both row and page compression for both tables and
indexes.
3.PAGE compression feature is a superset of ROW compression.
Page compression only occurs when the page is full to optimize the performance.

Reference: ( For the above information with clear example follow below link)

https://www.mssqltips.com/sqlservertip/1582/implementing-data-compression-in-sql-server-2008/

 

 

 

Advertisements