Compressing Partition Data
We will check the audit table in our database, which contains records regarding user activity.
The table is separated by a DATE column (per month) and has no binary columns. We are required by customer specification to keep DATA for 6 months, DATA must be available for inquiries and further investigations if necessary.
- Is there a way to compress old DATA (older than 6 months) into partitions?
- Are there any recommended considerations when compressing by section?
- What is the scenario when splitting or merging compressed partitions?
- Is there an effect when deleting a complete section?
thank.
source to share
Data compression works at the partition level. So, if you are into date partitioning, you can compress the "old" data as you like. As for when to use it, check out the BOL article here .
As far as partitions / partitions are concerned, BOL says this is a standalone operation, so there might be some downtime there.
With regard to deleting a partition, you usually create a new table with the same schema, execute an "alter table ... switch partition" statement, and then drop the new table. It should just be changing the metadata.
source to share