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.
With compression options (including columnstore) you are pretty stuck. LOBs are not supported for compression with the native process. You could try a SAN vendor that is MS certified for SQL Server that offers compression.