SQL Server Compression – Alternative Way to Compress NVARCHAR(MAX)

compressionsql serversql server 2014

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.

Best Answer

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.

Related Solutions

Sql-server – Index Strategies on Text or NVARCHAR(MAX) Fields

Why do you think anything but a scan should be used to pull back all the data? A full-text index won't really help - that helps you search those columns, but if you're just returning all the data (for any variety of WHERE clauses) then there's no shortcut to reading all of the data. Can I ask why a to_addr, which is presumably limited to ~320 characters by the SMTP standards (depending on which standard you believe), contains data > 4000 characters?

A lot of people think that a scan is bad. If you need to return a large amount of data, then often a clustered index scan will be used. Your where clause may lead to seeks being used to locate the rows to return, but a seek isn't going to work where the data in that column is that large. Are you just seeing a scan in the execution plan and assuming that must be the problem?

SQL Server 2012 – Indexing on Multiple NVARCHAR(MAX) Columns

One thing you might try is breaking those fields out into their own table(s). Then reference them by ID. You could then set up a UNIQUE index on those ID columns, and still use your hashing trick to ensure you have unique values in the new tables (if the values need to be unique there).

Example table(s):

[Shipper]
 ShipperID INT,
 ShipperName NVARCHAR(MAX)

[Consignee]
 ConsigneeID INT,
 ConsigneeName NVARCHAR(MAX)

ETC...

Unfortunately, with this type of design problem you have to get a little creative. Having that many NVARCHAR(MAX) columns is problematic on many levels.

Best Answer

Related Solutions

Sql-server – Index Strategies on Text or NVARCHAR(MAX) Fields

SQL Server 2012 – Indexing on Multiple NVARCHAR(MAX) Columns

Related Question