Data Compression in SQL Server

SQL Server support three types of Data compression which help to save storage space by reducing the size of the database, data compression has overhead on CPU, it is because for usage purpose CPU has to decode the compressed data. Data compression is not applicable on system tables. Enterprise and developer edition support Database compression.

Types of Data compression:

1. Row Level Compression
2. Page Level Compression
3. Unicode Compression

Data Compression can be configured on following configurations.
a. Clustered Table
b. Heap Table(A table without cluster index)
c. Non Clustered indexes
d. Indexed Views
e. On Individual partition of partitioned table or index

Row Level Compression
(i) Row level compression work by storing fixed length Char data types albeit they were stored as Variable length Varchar data types
(ii) It won’t stores NULL and 0 values
(iii) Reducing Meta data required for storing of data.

We can use following SQL commands for Row level compression on table Employee
ALTER TABLE Employee REBUILD WITH (DATA_COMPRESSION=ROW)

For partitioned table use below command
ALTER TABLE Employee REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = ROW)

For index, row level compression use below command assuming index idx_Employee_Id on table Employee
ALTER INDEX idx_Employee_Id ON Employee REBUILD WITH (DATA_COMPRESSION=ROW)


Page Level Compression
Page level compression, compress data by storing repeating or common value at one place and making reference to that value in table, when page compression applied then row level compression automatically applied.

Page level compression uses following techniques

(i) Row level compression is applied to store maximum number of rows on page
(ii) Column prefix compression is applied to store repeating values in header and reference is mapped with repeating/common values.
(iii) Dictionary compression scans for repeating values and store scanned info in page header


We can use following SQL commands for Page level compression on table Employee.
ALTER TABLE Employee REBUILD WITH (DATA_COMPRESSION=PAGE)

For partitioned table use below command.
ALTER TABLE Employee REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)

For index, page level compression use below command assuming index idx_Employee_Id on table Employee.
ALTER INDEX idx_Employee_Id ON Employee REBUILD WITH (DATA_COMPRESSION=PAGE)


Unicode Compression
Unicode compression enables database engine to compress Unicode values(NChar, NVarchar) stored in row and page compressed objects, we can use Unicode compression with fixed length NChar and NVarchar data types.
Unicode compression get automatically used accordingly when we use row/page compression.


If we want to implement data compression using wizard we can use following steps
1. Right click on table Employee then click storage and then manage compression as below



2. It will populate below window



3. From the below screen select row/page compression.


4. Select compression type as row then click next, it will populate below window



5. Click finish then it will the command in new window or we can use RUN immediately from above screen, here we have selected create script, then click finish.



USE [Capital]
ALTER TABLE [dbo].[Employee] REBUILD PARTITION = ALL
WITH
(DATA_COMPRESSION = ROW
)

it will generate above script as per our selection, we can execute script for row level compression.