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?
Sql-server – When would I want to use COMPRESS and DECOMPRESS functions in SQL Server 2016
compressionsql server
Related Question
- Sql-server – SQL Server 2016 replication between Windows 10 and Windows Server 2012
- SQL Server 2016 – High Compile Times for Functions with XML
- SQL Server – Should Deprecated MD5 Function Be Used?
- Sql-server – When to Use Functions over Stored Procedures
- SSRS 2017 – Can It Be Used with SQL Server 2016?
- SQL Server Performance – Is SCAN DENSITY a Valid Metric?
Best Answer
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.