Hello, I am an intern and was asked to do some research on SQL 2008 data compression. We want to store several parts of outlook emails in a table. The problem is that we want to store the entire email body in a field, but then want to compress it. Using Char() will not store the whole body, but will allow compression... using varchar() will store the entire body but not allow compression. Any ideas on how to store the whole body AND compress it?
Thank You for your replies!
SQL 2008 can do this for you, see http://msdn.microsoft.com/en-us/library/cc280449.aspx
Types of data compression in SQL Server 2008
Creating Compressed Tables and Indexes
Whitepaper: Data Compression: Strategy, Capacity Planning and Best Practices
Thanks for the replies. I looked over the links and learned quite a lot, but I'm still left with the same problem. When I used varchar to store the body of the emails I was able to keep the whole email, yet compressing the table saved very minimal space (saved .5 MB on a 36 MB test table). Once again, I'm not using Char because only the first 7,500 or so characters of the email get stored and we want the entire email in the table. Anyone have any suggestions how to work around this? Thank you.
Is it possible that the maximum possible size of your row exceeds the maximum allowable row size? You mentioned 7,500+ character emails so it sounds like you're getting close to the limit.
"A table cannot be enabled for compression when the maximum row size plus the compression overhead exceeds the maximum row size of 8060 bytes."
...from http://technet.microsoft.com/en-us/library/cc280449.aspx
Good luck!