SQL Server 2008 automatically compresses data stored in database. SQL server
does Lossless data compression.
server uses Dictionary based compression algorithm. Row Level
For Row level Data Compression SQL Server does
not use explicitly any standard compression algorithm. It works on very simple
- You created a column of CHAR(50)
- Normally SQL server requires 50 bytes regardless of the actual byte needed
by your Data.
- If you are storing "DEBUG MODE" in that column then you really need 10 bytes
So in Row Level Data Compression, rather than fixed
format data storage, SQL Server stores data in a variable
need to say at time of table creation that DATA_COMPRESSION =
ROWEstimate Row Level compression
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
In SQL server 2005 page level compression was
done by minimizing data redundancy but in SQL Server 2008 it is performed
- Reducing Data Redundancy
- Lossless Data Compression algorithm
- Column Prefix compression
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.
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 Estimate
Page Level compression saving
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.