I want to remember this study by Erin Stellato.
The summary:
If you’re in an OLTP system, you don’t want to return that many rows, so the tests here should give you an idea of how compression may affect performance. If you’re in a data warehouse, then you will probably see higher duration along with the higher CPU when returning large data sets. While the COMPRESS function provides significant space savings compared to page and row compression, the performance hit in terms of CPU, and the inability to index the compressed columns due to their data type, make it viable only for large volumes of data that will not be searched.