Compressing a text field in Sql Server 2k8 R2

So I have an application that stores a lot of text in a text field in SQL Server 2008 R2. I'm adding about 5000 records a day, and that is going to grow. The amount of data in the field can be between 4 KB and 100 KB.

I can change the field to be a blo开发者_如何学Cb field and store a byte stream in there (eg. zipped text), but I'm wondering if there is any compression option that I can use in SQL Server 2k8 (perhaps something designed for storing a lot of text?) that I could leverage using SQL Server out of the box?

thanks

---

SQL Server 2008 R2 has three compression options:

• row compression
• page compression (implies row compression)
• unicode compression

All three options only apply to data (rows), so none could help with large documents (BLOBs). So your best option is to compress/decompress in the client (ZIP). I would not consider this option easily, it means you're trading off queriability of the data.

---

In additional to row/page comperssion you can use FILESTREAMS to store field on compressed NTFS drive. But your files is not so big and comression will be a best choise.

---

Note:

Regarding compatibility of FILESTREAMS :

• FILESTREAM feature is available with all versions of SQL Server 2008, including SQL Server Express.
• SQL Server Express database has a 4 GB limitation; however this limitation does not apply to the FILESTREAM data stored in a SQL Server Express database.

However you need 'Developer Edition' or 'Enterprise Edition' for Row / Page compression.

alter table pagevisit rebuild with (data_compression=page);

Msg 7738, Level 16, State 2, Line 2 Cannot enable compression for object 'PageVisit'. Only SQL Server Enterprise Edition supports compression.