Sql-server – Sql Server Dynamic partition with page compressed index

clustered-indexindexpartitioningperformancesql server

I created a table with partitions by following this 2012 partitioned index document.

I created partitions monthly based on Date_Id column;

CREATE PARTITION FUNCTION Fnc_Prt_Fact_Sales (INT)
AS RANGE RIGHT FOR VALUES ('20200101', '20200201', '20200301','20200401')

CREATE PARTITION SCHEME Prt_Scheme_Fact_Sales
AS PARTITION Fnc_Prt_Fact_Sales
ALL TO ([PRIMARY])


CREATE TABLE [dbo].[Fact_Sales](
   [Slip_No] [nvarchar](155) NULL,
   [Date_Id] [int] NOT NULL,
   [City_Id] [int] NOT NULL,
   [Store_Id] [int] NOT NULL,
   [Sales] FLOAT
) ON Prt_Scheme_Fact_Sales(Date_Id)
GO

 CREATE  CLUSTERED INDEX [Ix_Fact_Sales] ON [dbo].[Fact_Sales]
(
    [Date_Id] ASC,
    [City_Id] ASC,
    [Store_Id] ASC
) WITH (
   DATA_COMPRESSION = PAGE ON PARTITIONS (1,2,3,4)
)  ON Prt_Scheme_Fact_Sales(Date_Id)
GO

i want to add partitions by dynamically (monthly). If i do that, how do i make data_compression include new added partition.
On Blog, partitions are written by manually.
–> DATA_COMPRESSION = PAGE ON PARTITIONS (1,2,3,4)

if it matters, i ue sqlserver 2017 standart edition.

Best Answer

The answer to your question, as asked, is to rebuild the new partition after the partition split. The split should be to an empty partition (so long as you split the rightmost partition befor you have any data to the right of the new boundary value), so this is not expensive.

eg

declare @newBoundary int = 20200501;
declare @newPartitionId int =  $partition.Fnc_Prt_Fact_Sales(@newBoundary);

alter partition function Fnc_Prt_Fact_Sales() split range (@newBoundary);

alter index ix_fact_sales on Fact_Sales
rebuild partition = @newPartitionId
with (   data_compression = page on partitions (@newPartitionId) )

But the easier, and more common approach is to set the index's default compression to PAGE, instead of specifying that only enumerated partitions are PAGE compressed.

 CREATE  CLUSTERED INDEX [Ix_Fact_Sales] ON [dbo].[Fact_Sales]
(
    [Date_Id] ASC,
    [City_Id] ASC,
    [Store_Id] ASC
) WITH (
   DATA_COMPRESSION = PAGE --ON PARTITIONS (1,2,3,4)
)  ON Prt_Scheme_Fact_Sales(Date_Id)
GO
Related Question