question

gotqn avatar image
gotqn asked

Effective 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.
compressionbackup-compressioncolumn-store-indexdata-compression
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

This site functions according to votes. For all helpful answers below, show this by clicking on the thumbs up next to those answers. If any one answer lead to a solution, indicate this by clicking on the check mark next to that answer.
0 Likes 0 ·
CirqueDeSQLeil avatar image
CirqueDeSQLeil answered
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.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Håkan Winther avatar image
Håkan Winther answered
According to this BOL article, you can benefit from PAGE compression on nvarchar(max) in SQL Server 2014: [ http://msdn.microsoft.com/en-us/library/ee240835.aspx][1] [1]: http://msdn.microsoft.com/en-us/library/ee240835.aspx
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.