Options
Changing the Compression on a table is not a schema change
Niall
Posts: 36 Bronze 1
As part of a system test I set compression on my database tables, (all 3200 of them) to NONE as I was investigating some of the issues with estimated cardinality being wrong on SQL Server 2008 R2. Typically we use PAGE compression to save space and improve perfromance (which it does).
Being lazy and havign paid a few quid for the tool I opted to re-set compression using the Red-Gate SQL Compare tool. Rather bizzarly the tool thought it had to un schema bind all the views for the tables to be altered to use PAGE compression... Which actually it does not as though an ALTER TABLE command changing compression does not alter the schema so schemabinding is not a relevant issue..
Additionally and more ineffeciently looking at the script that SQL Compare produces, primary key constraints are dropped and recreated, no doubt becuase the tool picks up that these go from being DATA_COMPRESSION=NONE to DATA_COMPRESSION=PAGE and scripts that. The same can also though be acheived by leaving the constraint in place and doing an ALTER TABLE to change the compression mode as the constraint has the same setting as the table. So this would had I run it been a very time consuming and wastfull step, given the amount of data in some of the tables.
Being lazy and havign paid a few quid for the tool I opted to re-set compression using the Red-Gate SQL Compare tool. Rather bizzarly the tool thought it had to un schema bind all the views for the tables to be altered to use PAGE compression... Which actually it does not as though an ALTER TABLE command changing compression does not alter the schema so schemabinding is not a relevant issue..
Additionally and more ineffeciently looking at the script that SQL Compare produces, primary key constraints are dropped and recreated, no doubt becuase the tool picks up that these go from being DATA_COMPRESSION=NONE to DATA_COMPRESSION=PAGE and scripts that. The same can also though be acheived by leaving the constraint in place and doing an ALTER TABLE to change the compression mode as the constraint has the same setting as the table. So this would had I run it been a very time consuming and wastfull step, given the amount of data in some of the tables.
Comments
I'm not sure if this is a bug or a feature request, but I see your point. In this situation there is no need to unbind the schema and drop the constraint.
There might be a scenarios where a simple alter table statement would be dangerous, or it might be that the performance implications were not thoroughly considered. I've logged this for our developers to look at under the internal tracking code SC-5055.
Thanks for taking the time to let us know about this.