Data Compression in SQL Server 2008

No.of Views1673
Bookmarked0 times
Downloads 
Votes0
By  Dhananjay Kumar   On  17 Jan 2011 09:01:25
Tag : Sql Servers , Server Management
SQL Server 2008 automatically compress data stored in database. SQL server does Lossless data compression.
emailbookmarkadd commentsprint

Images in this article missing? We recently lost them in a site migration. We're working to restore these as you read this. Should you need an image in an emergency, please contact us at info@codegain.com

 

Introduction

SQL Server 2008 automatically compress data stored in database. SQL server does Lossless data compression. 

Image Loading

SQL server uses Dictionary based compression algorithm.

Row Level Data Compression

For Row level Data Compression SQL Server does not use explicitly any standard compression algorithm. It works on very simple algorithm. Say,

1.    You created a column of CHAR(50)
2.    Normally SQL server requires 50 bytes regardless of the actual byte needed by your Data.
3.    If you are storing “DEBUG MODE” in that column then you really need 10 bytes of storage.

So in Row Level Data Compression, rather than fixed format data storage, SQL Server stores data in variable format.

Image Loading

We need to say at time of table creation that DATA_COMPRESSION = ROW

Estimate Row Level compression saving 

Image Loading

In above query dbo is name of the schema and TempTable is name of the table and ROW parameter says to estimate Row level estimation.

Page Level Data Compression

In SQL server 2005 page level compression was done by minimizing data redundancy but in SQL Server 2008 it is performed by

1.    Reducing Data Redundancy
2.    Lossless Data Compression algorithm
3.    Column Prefix compression

Image Loading

With column prefix compression first SQL Server identifies repeated byte sequence in beginning of column in all rows on the page. If same column is having same byte pattern in more than one row then SQL server stores the byte pattern once and replaces the other byte patterns with the pointer.
SQL Server creates Dictionary per page and stores repeating vales of the page in the dictionary and performs the compression on Dictionary.

Compression Saving   is directly proportional to repeated byte patterns 

Image Loading

Estimate Page Level compression saving 

Image Loading

In above query dbo is name of the schema and TempTable1 is name of the table and PAGE parameter says to estimate Row level estimation.

 
Sign Up to vote for this article
 
About Author
 
Dhananjay Kumar
Occupation-Software Engineer
Company-Infosys Technolgies,Pune
Member Type-Gold
Location-India
Joined date-20 Jul 2009
Home Page-http://dhananjaykumar.net/
Blog Page-http://dhananjaykumar.net/
Dhananjay Kumar is Microsoft MVP on connected system. He blogs at http://dhananjaykumar.net/ . You can follow him http://twitter.com/debugmode_/ and reach him at dhananjay.25july@gmail.com
 
 
Other popularSectionarticles
Comments
There is no comments for this articles.
Leave a Reply
Title:
Display Name:
Email:
(not display in page for the security purphase)
Website:
Message:
Please refresh your screen using Ctrl+F5
If you can't read this number refresh your screen
Please input the anti-spam code that you can read in the image.
^ Scroll to Top