IndexOptimize compression parameter

[sivey42]

I've been thinking about adding a @DefaultCompression flag to the IndexOptimize proc. Default to none, but options for page & row compression to add way to compress indexes as they are rebuilt. For me, this would be useful for "SQL on Azure VM" scenarios where we tend to see more CPU than memory in a box, and compressing pages helps to make better use of the limited RAM in the box.

So - looking for feedback. If the community thinks this would be useful, I'll volunteer to do the work. If the consensus is that it could be misused or abused, I'm OK with that too and will just skip it.

[cdp293]

I think it would be a nice feature to have, perhaps with a size threshold.
For the record, my use case is a bit different: recently started maintaining a db server with hundreds of db with pretty big tables and lots of big indexes. Adding compression makes query run faster on those huge tables and it helps to limit the disk space requirements.

Related feature: since we are talking about altering indexes, it would be great to be able to specify a target filegroup for the indexes whenever an index rebuild is done (helpful to tidy up not-so-tidy dbs and reclaim progressively some disk space)