Use of table compression in SQL Server 2014 I am going through articles about table compression in SQL Server. To me it seems quite easy to setup compressed tables and the benefits make the decision no brainer.

I wanted to consult with this community and see if there is any gotcha that I need to know. I am concern that the table compression feature might not be as simple as it looks. Could there any unwanted side effect when I migrate my tables to compressed table?
In my experience there are a couple of things to consider:


Sometimes, perhaps due to some maintenance task which doesn't respect compression settings, indexes and tables have compression turned off so carefully consider any routine tasks you have which rebuild indexes.
Space saving can be significant
When a query against a compressed table/index reads data from disk it was quicker (in my experience) than a query reading an uncompressed table due to the I/O limitations of disk.
When the pages were already in the buffer pool, though, the query was slower than when compared to uncompressed data because of the additional CPU involved in uncompressing the data.


So consider your situation in regards to CPU, RAM and storage capacities. Don't forget to consider columnstore indexes which offer both good compression and query performance.