SQL Server 2016 is offering a new function, COMPRESS (and it’s counterpart, DECOMPRESS) that will gzip the contents of the information you pass to them.
As you can imagine, these are great when applied to text values, and will have lesser impact when applied to already compressed things like some image formats and the like. But the real value are those columns where you allow for text input, and would like the compress the results down when storing the information in the table.
I know many times comment columns and other areas where you allow for more free-form entry of information can be easy targets for compression. I’ve seen people write custom libraries and use utilities to try to minimize data sizes coming into the database, and this new functionality will make it much more straightforward.
I’m curious about the trade offs however – and will be playing with it in the context of full-text search and longer columns that are not Text, but may be indexed. While the use cases for that are smaller, I’m assuming it will have the similar impact as encryption does, making searches against the values require a compress, search, decompress cycle. This means indexes on the columns may be less impactful, if true.
This may also mean that if you’re offering other search options on the values in a compressed column that they will have to be re-structured – or even avoided – as it would require almost a row-by-row read, decompress, compare, move on type strategy to work through the information (as it seems would be the case on a LIKE search).
There is mention of some overhead as well as the compression/decompression occurs.
But all of this seemingly negative aside, I think the function is a great tool. Comments, feedback, descriptive text that can get larger, etc. These are some great uses of the function – and can save and help minimize information stored. We’ve manually used gzip type compression in the past with projects and the savings can be remarkable.
What types of use-cases could this prove effective for in your own installation?