Data Compression of MS SQL Server

Introduction

The performance of MS SQL Server depends on Disk I/O Efficiency. If the data is in MS SQL server is compressed the performance increase as a fewer pages is required.

Microsoft SQL Server 2008 Enterprise Edition supports the Data Compression mechanism that we discuss in this article.

Where we can Configure it

The data compression can be configured on Table, Clustered – Index, Non Clustered – Index, Index Views and Partition of Table or on Index.

Levels of Data Compression

The Data Compression can be implemented on two levels

1.    Row

2.    Page

Row level Data Compressions

The main objective is more rows can fit into a single data page.

1.    The overhead of metadata of the records is reduced.

2.    Row-level data compression is turning fixed length data types into variable length data types, freeing up empty space.

Let's take an example to understand the point no-2

Suppose 120 are stored in an INTEGER data type. We all know that INTEGER data type takes 4 bytes to store, So the value 120 also take 4 bytes to store.

We also know that INTEGER value between 0 to 255 can store on 1 Bytes. So after compression 3 bytes release and our value 120 takes 1 byte to store.

3.    It also has the ability to ignore zero and null values, saving additional space.

Page level Data Compression

It begins with row-level data compression. With his it takes two additional compression features called prefix and dictionary compression.

Prefix compression

In this technique finds duplicate prefixes on a page for each column, and replaces each duplicate with a small reference number.

Dictionary compressionIn this technique finds duplicate values on a page, collects them into a dictionary stored after the page header but before the data rows, and replaces the duplicate values with their corresponding offsets in the dictionary.

Example of Data Compression In MS SQL Server

Step – 1 [ Create The Base Table ]

IF OBJECT_ID(N'dbo.tbl_EMPLOYEE', N'U') IS NOT NULL

   BEGIN

     DROP TABLE [dbo].[tbl_EMPLOYEE];

   END

GO

CREATE TABLE [dbo].[tbl_EMPLOYEE]

   (

     EMPID    INT,

     EMPNAME  CHAR(50)

   )

GO

Step – 2 [ Insert Some Records ]

INSERT INTO [dbo].[tbl_EMPLOYEE]

     (EMPID, EMPNAME)

VALUES(1, 'Joydeep Das');

GO 5000

Step - 3 [ Check the Space used by Original Table ]

EXEC sp_spaceused tbl_EMPLOYEE

Name	Rows	Reserved	Data	Index_size	Unused
tbl_EMPLOYEE	5000	200KB	152KB	8KB	40KB

Step – 4 [ Now we Used Data Compression ROW ]

ALTER TABLE [dbo].[tbl_EMPLOYEE]

REBUILD WITH (DATA_COMPRESSION = ROW);

GO

Step – 5 [ Now Again Check the Space Used After Data Compression ROW ]

EXEC sp_spaceused tbl_EMPLOYEE

Name	Rows	Reserved	Data	Index_size	Unused
tbl_EMPLOYEE	5000	144KB	120KB	8KB	16KB

Step – 6 [ Now we Used Data Compression PAGE ]

ALTER TABLE [dbo].[tbl_EMPLOYEE]

REBUILD WITH (DATA_COMPRESSION = PAGE);

GO

Step – 7 [ Now Again Check the Space Used After Data Compression PAGE ]

EXEC sp_spaceused tbl_EMPLOYEE

Name	Rows	Reserved	Data	Index_size	Unused
tbl_EMPLOYEE	5000	80KB	64KB	8KB	8KB

Step – 8 [ Now We Comare all Again ]

Original Without Compression

Name	Rows	Reserved	Data	Index_size	Unused
tbl_EMPLOYEE	5000	200KB	152KB	8KB	40KB

Compression ROW

Name	Rows	Reserved	Data	Index_size	Unused
tbl_EMPLOYEE	5000	144KB	120KB	8KB	16KB

Compression PAGE

Name	Rows	Reserved	Data	Index_size	Unused
tbl_EMPLOYEE	5000	80KB	64KB	8KB	8KB

Compression Type	Data
NO Compression (Original)	152KB
Compression ROW	120KB
Compression PAGE	64KB

Hope you like it.

Posted by: MR. JOYDEEP DAS