.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Articles >> DataBase >> Post New Resource Bookmark and Share   

 Subscribe to Articles

Row and Page Data Compression in SQL Server 2008

Posted By:Dhananjay Kumar       Posted Date: January 17, 2011    Points: 75    Category: DataBase    URL: http://www.dotnetspark.com  

Row and Page Data Compression in SQL Server 2008. SQL Server 2008 automatically compresses data stored in database. SQL server does Lossless data compression.
 

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


1.gif

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 a variable format. 


2.gif

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


Estimate Row Level compression saving 


3.gif

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 

4.gif



With column prefix compression first SQL Server identifies repeated byte sequence in beginning of column in all rows on the page. If the same column has the 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 


5.gif

Estimate Page Level compression saving


6.gif

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

     

Further Readings:

Responses

No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More Articles on C#, ASP.Net, Vb.Net, SQL Server and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend