March 24, 2011 at 9:03 am
Hello,
Can any one recommend a way of compressing NTEXT data using SQL Server Compression?
I have enabled it, but apparently it will NOT compress NTEXT(MAX), I also converted to NVARCHAR(MAX) with same result.
I also understand that data which is out-of-row data will not be compressed.
Data in my NTEXT column can be up to 1MB in size.
Does this not defeat the object of compression, where larger data will go off page, or be in a variable of MAX type.
My database is around 1TB in size, with 90% of this data taken up in a single column of one table. I can partition the table and move the data around to different spindles, but would really like to reduce the size of the database.
My problem is that I need to retain more data, and have limited disk space.
Any advice appreciated.
Thanks
Paul
March 24, 2011 at 1:25 pm
First Unicode compression was added in SQL Server 2008 R2, so if you don't have R2 then you can't compress any NCHAR or NVARCHAR columns. Second, NVARCHAR(MAX) is never compressed according to BOL: http://msdn.microsoft.com/en-us/library/ee240835.aspx
From that page:
Supported Data Types
--------------------------------------------------------------------------------
Unicode compression supports the fixed-length nchar(n) and nvarchar(n) data types. Data values that are stored off row or in nvarchar(max) columns are not compressed.
Note: nvarchar(max) data is never compressed even if it is stored in row.
So if you want it compressed you will have to do it yourself. You could do that in your application, via a UDF, or CLR. (Of course doing that would have huge repercussions in the ability to query the contents of the compressed column(s).
March 24, 2011 at 1:33 pm
Depending on your needs you might be better off going with a VARBINARY(MAX) field stored via FILESTREAM on a drive with 4 KB clusters so that you can turn the OS file compression on for the data. That would move 90% of your data out of the MDF/NDF files.
March 24, 2011 at 1:37 pm
Based on the abilities of compression and the version of SQL Server, you may want to look into an alternative method to compress it.
Also, I would look into changing that NText field to an NVarchar field
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 25, 2011 at 4:11 am
Thanks very much for your suggestions, I will now look at the different options.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy