Page Compression for Out-of-Row LOB Storage
Disappointingly page compression does not apply to out-of-row LOB storage for data types such as VARCHAR(MAX) and NVARCHAR(MAX) [and also TEXT and NTEXT].
Would you consider adding this in a future release?
Upvotes: 10<=-=Mar 8 2011 9:34AM=-=>
Thanks for contacting SQL Server team with the feedback. We will consider this in a future release. At this time, you can compress Out-of-row BLOBs by storing them as FileStream on a compressed volume. But I do understand that this is not your scenario as the data types you mentioned are not suitable for Filestream
Sunil, thanks for the feedback. I had considered this option, but we’re using database mirroring as a resiliency solution which, unfortunately, doesn’t support Filestream.
Chris, I updated the title of your request. You are not really saying PAGE compression for out of row or LOB data. What you are saying is that we need the ability to compress this data as well. Also, I added a variance of UNICODE compresion as well.
Chris, Sorry for the delayed answer. We support FS with AlwaysOn so at least partially your issue can be handled. We still don’t have support for in-row MAX data types. For now, I am closing this connect as we have not addressed this in two releases. Please create a new connect request if you feel strongly about it
Solomon Rutzky commented
This absolutely needs to happen. Please, please do this. And it should include the VARBINARY(MAX) and XML datatypes as well. I assume that doing this for one LOB type would extend to all LOB types, but just in case it doesn't I figured I would add those to the request.
Please note that in-row MAX data _can_ be PAGE compressed, but only if it is under a certain size. I tested with VARCHAR(MAX) and saw that 6000 character/byte rows would not compress, but 4000 character/byte rows did.
On a related note, please see "Native UTF-8 Support in SQL Server 2019: Savior or False Prophet?" ( https://sqlquantumleap.com/2018/09/28/native-utf-8-support-in-sql-server-2019-savior-false-prophet-or-both/ ) for a detailed analysis of the new UTF-8 feature.
Jeff Moden commented
I second the problem. We are frequently required to delete blob data by setting it to NULL in a mostly vain attempt to recover disk space. LOB_COMPACTION simply does not work for out of row storage and it really needs to. It was quite the surprise, as well, because all of the documentation suggests that it will.
Referring to the final message above by Sunil, do we really need to open a new item? Can't you just take this one to the bank?