I've been reading the docs (!) but can't nail this down for sure. If you create a compressed column, is compression / decompression carried out on the server or the client?
I'm looking at storing a lot of base64 text and want to limit the amount of stuff I'm pushing up and down the network (I'm much less fussed about disk space). Obviously, if the SQLA client does the (de)compression that's great, otherwise I should probably do my own (un)zipping first.
asked 23 May '11, 08:23
The term "compressed columns" means that the column data is compressed when it is stored in the database. You can tell the database server that you want the column data to be compressed (when stored in the database file) by adding the COMPRESSED keyword to the char, varchar, or long binary/varchar column in your table definition. Here is an example from the docs:
CREATE TABLE t ( filename VARCHAR(255), contents LONG BINARY COMPRESSED );
Using compressed columns will have no effect on the quantity of data which is exchanged between client and server. To decrease the amount of client-server traffic you need to enable communication encryption by adding the COMPRESS=YES connection parameter to your connection string.
You may also want to read about the compression threshold connection parameter - the COMPTH parameter controls when compression will be performed in order to get the best mix of uncompressed and compressed packets - e.g. there is no need to take the time to compress short packets (packets smaller than the size of a TCP packet).