When would I want to use COMPRESS and DECOMPRESS functions in SQL Server 2016?

Question

In what live operational or development situation /requirement would I want to use the Compress function?

Like when should I become thankful for Compress and Decompress functions introduced in version 2016?

Answer 1

Use them when you want to achieve the benefits and you are prepared to pay the costs.

Compression can significantly reduce the number of bytes required to represent a large value. This saves the cost of disk, reduces the time to take backups, and restore after an incident. Performance can improve as fewer bytes must be pulled off disk and passed over the network.

The cost is in CPU to (de)compress the value before it can be used. This adds latency, consumes cycles which aren't available for real work and burns electricity.

So if you have large objects (compression would make a significant difference) such as text or XML (compress well) that are used used infrequently (minimize overhead), disk is expensive and CPU is cheap compression maybe helpful.

If you're storing jpg (already compressed), are on a SAN (built-in compression & de-dupe, likely fast network) and on a small, busy server then likely compression is not beneficial.