Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Data Compression of MS SQL Server

DZone's Guide to

Data Compression of MS SQL Server

· Performance Zone
Free Resource

Evolve your approach to Application Performance Monitoring by adopting five best practices that are outlined and explored in this e-book, brought to you in partnership with BMC.

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

Learn tips and best practices for optimizing your capacity management strategy with the Market Guide for Capacity Management, brought to you in partnership with BMC.

Topics:

Published at DZone with permission of Joydeep Das, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

The best of DZone straight to your inbox.

SEE AN EXAMPLE
Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.
Subscribe

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}