Is it possible to compress the data stored in text columns in SQL Server? We send out a lot of letters and we save each copy in our database. We now have four databases that we are using and about to get another one. I'm wondering, since this data isn't access constantly, if there is a way to COMPRESS the data stored in the text column and then use an uncompression utility when the data needs viewing.

I'm wondering if there is something native in SQL to do this, or if there is something that works with Cold Fusion where I can compress the data BEFORE I save it and then uncompress it whenever I need to read it.

Thanks in advance for any input.

By using varChar over text you will save tons of space.
varChar fields expand to hold the amount of data you put in, while char and text fields will always be the maximum size even if there is nothing in them.
varChar in SQL Server 2000 can hold up to 8000 characters, which is a pretty hefty sized letter.

I'm unaware of, but there may be a 3rd party product for SQL to do this, but beware, we don't like installing these on our shared SQL servers.

Other options (never tried this) would be to zip the file (see cflib.org) I think there is a function on there to zip and unzip, using Java. Then save the .zip file to the database. I'm saying stored in an image or text field. If the zip files are small enough you can get away with varbinary or varchar fields. <cfqueryparam type="BLOB" is what you may need to use to store the zip file in the DB if you wind up using a image field.

Not sure if the zip file is a binary or text?

Play around with it and see how it goes.

http://livedocs.macromedia.com/coldfusion/6.1/htmldocs/tags-b20.htm#wp1102474