CODEWITHSUNDEEP
sql-serversql-server-2008
kaes
kaes

Reputation: 1307

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 blob 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

Upvotes: 3

Views: 9533

Answers (3)

Simon_Weaver
Simon_Weaver

Reputation: 145890

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.

Upvotes: 2

ceth
ceth

Reputation: 45295

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.

Upvotes: 3

Remus Rusanu
Remus Rusanu

Reputation: 294227

SQL Server 2008 R2 has three compression options:

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.

Upvotes: 4

Related Questions