1

We are using SQL Server Express 2019 and have inevitably ran into the 15GB cap. I now have to figure out a way to quickly compress data since we cannot buy a license at the moment.

At the moment it is only one table (more specifically one column in A table) that is holding 9GB of data. This data is basically a history of production information that is queried by a C# program to generate PDF's and various other things. The data type of the column is NVARCHAR(MAX).

I mainly have to main concerns.

  1. What are the implications if I compress old data in this column? Will our application still get the uncompressed data when it is queried?

  2. What is the best way to go about compressing a column holding 9GB of NVARCHAR(MAX) data, and still being able to query this historical data with the same SQL queries so that the entire codebase's calls don't have to be re-written?

Aby help would be greatly appreciated. Kind regards

4
  • Can you provide more info, including the table schema, any indexes, est. # of rows etc?
    – HandyD
    Sep 10, 2021 at 11:31
  • There aren't any other relationships to this particular problem table but there are about 250 thousand records in this table alone. All spanning back to about 2017 Sep 10, 2021 at 11:59
  • @Charlieface It does. I just checked the average length of the column's records and they range from a few hundred all the way up to 3 million chars. Do you know of any other solutions? Sep 10, 2021 at 12:08
  • No, because page and row compression do not work on LOB data Sep 10, 2021 at 12:08

1 Answer 1

1

SQL Server offers a multitude of ways to apply Data Compression to a Table, without having to introduce any additional coding for handling the compressing and decompressing action itself. Unfortunately certain data types (BLOBs) such as NVARCHAR(MAX) are unaffected by such compression, and NVARCHAR in general is not a candidate for Row Compression. If you know you are using no more than NVARCHAR(4000) and can reduce the data column's data type to that, then it may be applicable to Page Compression.

Additionally you may find the answers to this similar DBA.StackExchange question helpful with alternative ways to compress your column. One of the answers touches on the built in function COMPRESS() which does help compress BLOB data types such as NVARCHAR(MAX) but will require you to modify some of your code to manually call it when inserting into the column and manually call the DECOMPRESS() function when reading from the column. If you do go this route, I'd recommend using a stored procedure (or two) as your centralized way of inserting and reading from that column so you only have to implement the calls in one place, and then using that stored procedure wherever was previously directly accessing the column, if possible.

Finally, I'd like to mention that the data limitation in SQL Server Express is a per database limit. If it's feasible, you could break your Table out into more specific subject matter across multiple Tables that are each stored in a separate database, to work around the limit, as an idea. But I'd imagine you'd have more coding changes than it's worth in doing so.

2
  • 1
    Some of the records hold 3 million characters in the NVARCHAR(MAX) column so I will have to go the stored procedure route or drop data into another DB instance. Thanks a lot for the answer though. Have a great weekend @J.D Sep 10, 2021 at 12:13
  • @IvanSnyman Yea that's a tough situation, unless there is some logical way to split up those records into more smaller records that still make sense, I'm not sure what else you can really do unfortunately. Best of luck with my suggestions! You too have a great weekend! 🙂
    – J.D.
    Sep 10, 2021 at 18:48

Not the answer you're looking for? Browse other questions tagged or ask your own question.