Alternative way to compress NVARCHAR(MAX)?

I am trying to compress some tables that have NVARCHAR(MAX) fields. Unfortunately, the row and the page compression do not have the desire impact (only ~100/200 MB saved for 20 GB table). Also, I am not able to apply column store and column store archival compressions because they do not support compression of NVARCHAR(MAX) fields.

Can anyone tell if I have any alternatives here?

I also guess the row and page compression do not have effect because the content of the NVARCHAR(MAX) columns is unique.

Both page and row compression do not compress BLOBs.


Because of their size, large-value data types are sometimes stored separately from the normal row data on special purpose pages. Data compression is not available for the data that is stored separately.

If you want to compress BLOBs you need to store them as VARBINARY(MAX) and apply your stream compression algorithm of choice. For example GZipStream. There are many examples how to do this, just search for GZipStream and SQLCLR.

StackExchange Database Administrators Q#78323, answer score: 17