When I generate database script in Visual Studio, the CREATE INDEX statement specifies DATA_COMPRESSION for each single partition:
CREATE CLUSTERED INDEX [ExampleIndex]
ON [dbo].[ExampleTable]([ID] ASC) WITH (DATA_COMPRESSION = PAGE ON PARTITIONS (1), DATA_COMPRESSION = PAGE ON PARTITIONS (2), DATA_COMPRESSION = PAGE ON PARTITIONS (3))
ON [ExamplePartitionScheme] ([ID]);
This creates problems during deployment because the number of partitions in the source code is not always the same as in the database. Therefore I would like to genereta CREATE INDEX statements that defines DATA_COMPRESSION for all partitions like this in stead:
CREATE CLUSTERED INDEX [ExampleIndex]
ON [dbo].[ExampleTable]([ID] ASC) WITH (DATA_COMPRESSION = PAGE)
ON [ExamplePartitionScheme] ([ID]);
I have already tried to change a bunch of the publish settings, but with no luck so far. All help is greatly appreciated!
I have tried to change various publish settings that target partitions and table options, but none of them have changed the generated statement to set DATA_COMPRESSION for all partitions.