In what table design/scenarios should I prefer a Row level compression and not Page level?
Like if we were to name one major advantage of one over another, and possibly one disadvantage of each compared to another, what would it be?
In this amazingly helpful in many ways article I see that Update% and Scan/Select estimations as well as actual estimated space gains are the decisive factors:
HOWEVER, if we really are not concerned about a slight difference (between Row and Page compression) in space saving after table is compressed but also want to ensure that further Read and Write operations to this table will perform at least not worse than before compression then what type should I choose ? (to minimize risk of possible issues with performance after compression).
Also, a sub-question:
after I compress a 500-million-row table, how can I un-compress it /rollback? Are there any pitfalls involved in rolling back?
( I am doing a detailed POC on applying compression to several largest tables in our environment, those tables are huge and critical, and I want to make sure I am well prepared for the unexpected). This will be the statement that I ll use to compress: (or do you suggest to do individual indexes instead of entire table? if yes then why would I want to do that?).
ALTER TABLE My2TBTable_1 REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE);
Voldemar likes to play CHESS (and IS good at it!)