Kendra Little explains the COMPRESS() function in SQL Server 2016:
One cool little feature in SQL Server 2016 is COMPRESS(). It’s a TSQL function available in all editions that shrinks down data using the GZIP algorithm (documentation).
Things to know about COMPRESS():
-
Compressed data is in the VARBINARY(max) data type
-
You get the data “back to normal” by using the DECOMPRESS function – which also outputs VARBINARY(max)
-
You can’t use columns of the VARBINARY(max) type in an index key column– but it may be useful to use the column as a filter in a filtered index, in some cases
COMPRESS() uses standard GZip compression, so you could use methods other than DECOMPRESS() to inflate the data—for example, bring the compressed data out to your application and use language-specific GZip libraries to decompress the data. Read the whole thing.