Sample Header Ad - 728x90

Dynamic partitions with page compressed index

0 votes
1 answer
474 views
I created a table with partitions by following this SQL Server 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 dynamically (monthly). If I do that, how do I make data compression include newly added partitions? In the blog, partitions are written out manually: --> DATA_COMPRESSION = PAGE ON PARTITIONS (1,2,3,4) If it matters, I use SQL Server 2017 Standard Edition.
Asked by rkapukaya (1 rep)
Dec 1, 2020, 03:52 PM
Last activity: Sep 5, 2024, 04:34 PM