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
Last activity: Sep 5, 2024, 04:34 PM