Failing to Acknowledge DATA_COMPRESSION Diffs at Table Level

howarthcd

I have found an inconsistency whereby SQL Compare (9.0.0.79) fails to acknowledge (at the table level) differences in the DATA_COMPRESSION option setting for indexes - the relevant tables on both sides of the comparison are shown as being equal even though the line-by-line differences are highlighted when viewing the scripts in the script pane at the bottom of the main window. Incidentally the 'Ignore...Data compression' project option was not selected.

Furthermore the synchronisation script includes statements to change the setting of the DATA_COMPRESSION option even though the table is not selected for synchronisation (the table can't be selected as it appears under the 'identical objects' area).

I would expect a difference in the DATA_COMPRESSION setting to cause SQL Compare to highlight that there is a difference between the tables.

Chris Auckland

Thanks for your post.

Would you be able to send me a screenshot of this? As far as I know, when you set the compression on a clustered index, it sets the compression for the table. Since the table can only have one clustered index, it can only have one compression state.

I believe this is why we only compare the compression state of the table at the table level, and ignore the compression state of an index.

howarthcd

Chris, it is possible to compress the clustered index and then selectively compress nonclustered indexes as desired - altering the compression state of the clustered index does not affect the compression state of the nonclustered indexes.

Likewise, any new nonclustered indexes that are created after the clustered index has been compressed are not compressed by default and have to have the DATA_COMPRESSION option included explicitly if compression is required from the outset.

Unfortunately I'm unable to replicate the exact problem that I was experiencing before as the databases have moved on since then.

Thanks
Chris

howarthcd

Chris, I've sent an email (to the support@red-gate.com email address) which includes a set of files that should allow you to partially recreate the problem.

Please let me know how you get on.

Thanks
Chris

Chris Auckland

Thanks for your help reproducing this.

I've experienced the same symptoms now, and it seems that when working with scripts, the compression state of the nonclustered indexes are ignored. Even though the setting is written to the file, it isn't considered in the comparison.

I've created bug SC-5113 for this. It also looks like this issue should be fixed in the next release of SQL Compare, but I haven't got an exact timeframe for that at the moment.