SQL Server LOB data compression and full-text search

Question

I have a SQL Server 2008 database with a large amount of varchar(max) data that is currently indexed with full-text search. Unfortunately, row-level compression in SQL Server 2008 does not support LOB data.

I am toying with the idea of using SQLCLR to compress the data and a custom iFilter to enable the data to be indexed with full-text search.

I'm interested in getting some feedback on this idea. Could it work? Has it been done before? What are the possible pitfalls? Can you recommend an better solution?

Answer 1

A long time ago, I built a mini-SharePoint, which would compress incoming files using a zip library, and store the bytes in a varbinary(max) column. Since the spec called for metadata as opposed to actual file contents, I didn't have to worry about Full Text Search.

You could achieve the same thing with CLR now. Pitfalls would be the CPU load during decompression of data for indexing during the search, but CPUs are fast now.

Option two? Buy more storage.