SQL Server 2008 Compression NTEXT

  • 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

  • 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).

  • 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.

  • 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

  • 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