This content has been marked as final. Show 3 replies
Partition row movement is logically equivalent to a delete from one partition, and an insert to another. As this is a row-by-row operation, the resulting data would be stored without columnar compression.
Would I be better off to wait on the compression until most of this type of activity has almost ceased? (The movement is because we sometimes have to change the period of retention for a class of records). If I wait a month and then compress the partition older than one month, I should not have too much movement of rows.
Would my queries suffer though?
Your approach sounds logical. As for query performance, there are many factors at play; it isn't inconceivable that your query performance actually improves with uncompressed data. You could also consider using OLTP compression for partitions representing time periods with high change rates.