Background
One of our SQL Server 2012 databases is getting a bit large, at least compared to our other databases. I was running some queries and noticed that we are currently storing large amounts of xml/html data in one of the columns. This is the main contributor to the size of the databse.
It occurred to me that the data in this column could be compressed prior to storage in C#. I found these compression methods that I was able to verify would work fine.
In my testing, the compression and decompression in C# for a 20,000 character html string took 1-3 thousandths of a second, and the decompression time was similar.
So, my thought is that compressing and decompressing the xml/html strings in C# would have the following advantages:
- Reduced amount of data to transfer TO the database
- Reduced amount of storage required IN the database.
- Reduced amount of data to transfer FROM the database.
All this would only be offset my a few thousandths of a second for the compression and decompression on the web servers.
The Question:
Are there any pitfalls to employing this strategy of which I need to be aware?