What are the challenges you face when working across database platforms? Take the survey
Options

How to get SCA to sync changes to index data compression settings?

I have updated an index on a table to use Data Compression = Page, but SQL Change Automation is not identifying that anything has changed for that table. How do I get it to identify that the WITH settings for the index have changed so that it will generate a migration script that can be deployed?

Index WITH statement before:
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF)

Index WITH statement now:
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF, DATA_COMPRESSION = PAGE)

I used an ALTER INDEX REBUILD WITH statement to add the data compression to the index that did not previously have it.

The SCA project is setup with this setting, which I thought would enable it to see this particular type of change, but I guess not: <SyncOptionIgnoreDataCompression>False</SyncOptionIgnoreDataCompression>

Answers

  • Options
    Hi EKelly

    That is indeed the appropriate setting to change, please can you tell us a little more about your setup?

    Are you using a SQL Source Control Project as your source or is it exclusively SQL Change automation.
    What version are you using and what implementation please? (SSMS, VS, CLI, Azure plugin etc)
    Kind regards
    Peter Laws | Redgate Software
    Have you visited our Help Center?
Sign In or Register to comment.