SSDT Deployment Script wants to rebuilt table due to changed compression
The scenario is a SSDT project in which a big table that contains a primary key is partitioned and all partitions are initially uncompressed.
If after deploying the project to SQL Server a partition of the table is compressed, and the (unchanged) SSDT project is being re-deployed, the deployment script contains code to completely rebuilt the (huge) table which is not practical on a production system. Since I have checked both "Ignore partition schemes" and "Ignore table options" in the Advanced Publish Settings, I would expect SSDT to not include any changes on the fact table in the deployment script.
Tony Donley commented
I don't want to "ignore index options". I want them carefully considered and explicitly specified in source control so they are deployed consistently in every environment.
When the only difference is the compression setting, I don't expect the engine to generate a long table rebuild. I expect and ALTER INDEX...REBUILD statement with the appropriate options.
I would also love to see the SORT_IN_TEMPDB option honored.
Toe Lwin commented
If your partitioned table has clustered index which I think why ignore_table_option doesn't work, try setting ignore_index_options to true.