Data Compression of MS SQL Server

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  VARCHAR(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 ]

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

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