peter-757102 (9/19/2012)
Personally I would happily use compression on some kinds of data, but unfortunatly the primary type of data that comes to mind (blobs) can't be compessed in ány version of SQL Server (to my knowledge). This unfortunatly makes compression in SQL Server really not that usefull as blobs with auxialiary data, not required by the relational engine is what often consumes most of the space in big import tables for example. On the main active tables blobs are often used for user comments and verbose change logs. Thus in the area where compression would be most welcome, there is none available.
The one truely always beneficial compression is backup compression and that is restricted to certain versions, again, major a downer.
Some of the third party products do compress blobs. SQL Storage Compress from Red Gate will compress them in the db, though I don't believe it does filestream data.
Disclosure: I work for Red Gate.