Do you have a comment or suggestion to improve SQL Server? We’d love to hear it!

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?

4 votes
Sign in
(thinking…)
Sign in with: oidc
Signed in as (Sign out)

We’ll send you updates on this idea

AdminMicrosoft SQL Server (Product Manager, Microsoft Azure) shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

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

Thanks
Sunil

<=-=Mar 8 2011 1:30PM=-=>

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.

Thanks
Chris

<=-=Jan 15 2012 11:47AM=-=>

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.

thanks
Sunil

<=-=Dec 1 2014 9:06PM=-=>

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

thanks
Sunil

2 comments

Sign in
(thinking…)
Sign in with: oidc
Signed in as (Sign out)
Submitting...
  • Solomon Rutzky commented  ·   ·  Flag as inappropriate

    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  ·   ·  Flag as inappropriate

    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?

Feedback and Knowledge Base