Im a little late reading this, but have a couple comments to add.

My first thought was to agree with Ian, that I think this would be better done in the middle tier. It would reduce the bandwidth used, reduce the db server load, and still give you the space savings. Since it would be almost the same chunk of code, it's a 'where should the code go' question. The only downside I see to middle tier placement is that if someone wants to do some type of bulk load or update, they'll have to be sure to use the same compression method/code. Actually, I think I still agree with Ian!

I don't see this as a bad use of the CLR. If you've decided to do it on the server, clearly CLR provides the best way to accomplish it.

I'm not sure I agree with Peter that it would kill server performance. Not sure what the tradeoff would be, would guess cpu and memory usage would go up, disk io would decrease as would space usage. If you needed to optimize the compression could be done asynchrously, with a flag on the table indicating compressed or not, job running behind to handle the compression. Not much you could do about uncompression though, unless you went with a split solution where you handled compression on the server, uncompression on a different tier. Might not be best fit for things like serving images, but it would probably do well for something like document storage.

I think it parallels how we do compression in the file system. Sometimes we just mark the folder as compressed, sometimes we compress the file manually. Which is better or more correct?

I also think that if SQL offered it as a native option you'd probably see a lot of people use it.