All geek questions in one place

Column level compression in SQL Server

I have a column in which I would like to store a lot of text data in (XML data). About 8000 characters per line and about 100-500 lines per minute.

A significant amount of data means that I will have to clean the column pretty neatly. (Since I need to host my SQL Server in our SAN, the storage space is quite expensive.) But if I can find a way to compress this data, I can save it longer.

I have seen things like this article on using CLR integration to compress BLOB files in SQL 2005.

I also saw the SQLCompress.NET tool for SQL Server 2005.

Both seem to say that they are doing exactly what I want. Compression of the data of one column during its storage.

However, the tool has been abandoned (no updates since 2008), and I know little about CLR Integration, except that I heard that this can cause problems. In addition, both of these solutions are for SQL Server 2005.

So here is my question. I am using SQL Server 2008 R2. Will any of these SQL Server 2005 solutions work well for me?

Or is there another solution that I can use to compress my data?

NOTE. Line compression and page compression will not help in what I need (at least as far as I can see). Line compression stores fixed-length data in variable-length fields, and page compression reduces instances of redundant data. None of them will help with large blocks of text.

NOTE II: I saw this question , but its answer uses line and page compression or FILESTREAM. I do not want to use FILESTREAM because I am losing the ability to mirror my database.

+4
source share
2 answers

I think it’s best to use the client library to compress and decompress the data before you put it in SQL Server, and if you want to request specific XML elements or attributes, you can extract these and store them in separate columns or normalized rows ( which you want to do anyway - querying large columns of XML text, especially for nested elements, is slow).

0
source

You can try using filestream to store xml documents and use NTFS to compress them.

Watch this artcle

Using Filestream in SQL2008

0
source

Source: https://habr.com/ru/post/1393415/

More articles:


All Articles