Geek Answers Handbook

XML column compression in SQL Server 2008 R2

Is there a direct way to compress a column of XML data in the standard edition of SQL Server 2008 R2?

My limitations:

  • Standard version of SQL Server 2008 R2;
  • Must store the original XMl in the database;

Earlier, I asked a similar question about generalized text storage. Text field compression in Sql Server 2k8 R2 , the result of which was the use of FILESTREAMS. If I do this here, although I will lose all the XML functions that SQL Server provides that I would like to use.

+4
source share
2 answers

You can always store XML as NVARCHAR, and then use the XML functions in the view against this column, which first converts the data to XML. However, you will have to weigh how much the work while compensating for the benefits of compression (the processor load will be significant, proportional to the amount of compression that you actually achieve). I can understand the desire to do such things on an I / O-linked system, but I suspect that XML columns are not candidates for compression for precisely this reason - the costs will outweigh the benefits more often than not. If you really intend to store data as compressed, and your biggest problem is storage space, I would think about letting your application do compression (C # here is much more flexible than T-SQL, and I suppose and also let your application deal with XML functions.

+4
source

PAGE / ROW COMPRESSION , the only "easy way" that I know does not work for XML columns, because it does not work for a LOB page. Works using CLR-implemented GZIP compression, as explained in this very good article . One of the drawbacks is that all XML must fit into memory when executing queries. In addition, it can be much slower, so "you have to be careful and take all ... factors into consideration."

0
source

More articles:


All Articles