Partition Data compression

Question

We’ve an Audit Table in our DB, which holds records concerning the user Activity .
The Table is partitioned by the DATE column (Per month), and doesn’t have any binary columns . We are obliged due to customer specification to keep the DATA for 6 months , the DATA should be available for Querying and further Investigations if needed.

1. Is there a way to compress old DATA ( older than 6 months ) by Partition ?
2. are there any recommended considerations when compressing by Partitions ?
3. what is the scenario while splitting or merging compressed Partitioned ?
4. is there any effect while deleting a complete partition ?

Thx.

Answer 1

Data compression works at a partition level. So, if you're partitioning by date, you can compress "old" data as you see fit. As for when to use it, check out the BOL article here.

As to merging/splitting partitions, BOL says that this is an offline operation, so you may incur some downtime there.

As for deleting a partition, you'd typically create a new table with the same schema, do an "alter table...switch partition" statement and then drop the new table. That should be just a metadata change.