November 24, 2014 at 8:24 am
From Online Help:
When partitions are split by using the ALTER PARTITION statement, both partitions inherit the data compression attribute of the original partition.
ALTER TABLE <table_name>
REBUILD PARTITION = 1 WITH (DATA_COMPRESSION = <option>)
So I only need to rebuild the most recent partition and all partitions to follow would be compressed, it seems.
I have 2 tables with 165 partitions - partitioned daily - 6 million records/partition for both tables. One of the tables isn't used very much so I' want to compress it to save some space.
I think the next thing I'd have to watch is that when the partitions that are compressed get to point of switch out (165 days later), I have to code switch-in table for compression.
From Online Help:
To switch a partition, the data compression property of the partition must match the compression property of the table.
November 26, 2014 at 11:50 am
Ran on most recent partition:
ALTER TABLE xxxx REBUILD PARTITION = 165 WITH (DATA_COMPRESSION = PAGE)
The next partition via split function was also compressed at page level. Only had to run ALTER TABLE to begin the cycle of compression on new partitions. Sliding window partitioning strategy.
165 days from now, when these new partitions are ready to be switched out, the switch-in table has to be compressed, if I don't rebuild all partitions before then and make adjustments to the switch-in table at time when all partitions are compressed.
So it is true, mixed compressed partitions can co-exist with partitioned table.
If you have a lot of partitions and oldest are not used very much or an entire partitoned table isn't used much, can compressed most unused partitions to save space. CPU will be used more to unpack so if CPU has little further capacity, forget it.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply