CODEWITHSUNDEEP
sqlsql-serverdatabasepartitioncolumnstore
Ivan Lin
Ivan Lin

Reputation: 33

SQL Server partition by month use persisted column and create clustered column store index at the same time

I created a partitioned table in SQL Server as follows:

--Add File Groups
ALTER DATABASE TEST ADD FILEGROUP JAN;
GO
ALTER DATABASE TEST ADD FILEGROUP FEB;
GO
ALTER DATABASE TEST ADD FILEGROUP MAR;
GO
... to DEC

--create files 
ALTER DATABASE [Test] 
ADD FILE (NAME = N'JAN', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\Part_JAN.ndf' , SIZE = 5120KB , FILEGROWTH = 1024KB ) TO Filegroup [JAN]

ALTER DATABASE [Test] 
ADD FILE ( NAME = N'FEB', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\Part_FEB.ndf' , SIZE = 5120KB , FILEGROWTH = 1024KB ) TO Filegroup [FEB]

ALTER DATABASE [Test] 
ADD FILE ( NAME = N'MAR', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\Part_MAR.ndf' , SIZE = 5120KB , FILEGROWTH = 1024KB ) TO Filegroup [MAR]
... to DEC

--Create Partition Function & Partition Schema
DROP PARTITION FUNCTION partition_ByMonth

CREATE PARTITION FUNCTION partition_ByMonth (int) 
    AS RANGE RIGHT FOR VALUES (2,3,4,5,6,7,8,9,10,11,12) ;
GO

-- Creates a partition scheme called myRangePS1 that applies myRangePF1 to the four filegroups created above
CREATE PARTITION SCHEME Partition_Schema_ByMonth
    AS PARTITION partition_ByMonth
    TO (JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC) ;
GO

--Create Table
CREATE TABLE [TEST].[TEST_PS_MONTH]
(
    [ID] [varchar](4) not null,
    [VALUE] [varchar](30) null,
    [ETL_Date] [datetime] not null, 
    [ETL_Month] as MONTH([ETL_Date]) PERSISTED,

    CONSTRAINT(AK_TEST_PS_MONTH) UNIQUE NONCLUSTERED
    ([ID] ASC, [ETL_Month] ASC)
               WITH (PAD_INDEX= OFF) ON Partition_Schema_ByMonth([ETL_Month])
) ON Partition_Schema_ByMonth([ETL_Month])
GO

Then I want to create clustered column stored index

---- create clustered column store index (col compression)
CREATE CLUSTERED COLUMNSTORE INDEX [IDX_TEST_PS_MONTH]
ON TEST.TEST_PS_MONTH
WITH (DROP_EXISTING = OFF, COMPRESSION_DELAY = 0) ON Partition_Schema_ByMonth([ETL_Month])
GO

But I get the following error

Msg 35307, Level 16, State 1, Line 177
The statement failed because column 'ETL_Month' on table 'TEST_PS_MONTH' is a computed column. Columnstore index cannot include a computed column implicitly or explicitly.

Is there a way to compress a table that has been partitioned by computed month?

Thanks!

Upvotes: 0

Views: 1151

Answers (1)

Dan Guzman
Dan Guzman

Reputation: 46203

Since your objective is to maintain a 12 month sliding window based on the the ETL_Date month, you can partition on ETL_Date directly without a computed column. Create a partition function with initial monthly date boundaries. Then run monthly maintenance to truncate the oldest month data, remove the old boundary, and create a new boundary for the upcoming month. I suggest you use a single filegroup unless you have a need to do otherwise.

Below is an example of an initial setup for this strategy with 13 month boundaries (2020-01-01 through 2020-01-01).

CREATE PARTITION FUNCTION partition_ByMonth_Function (datetime) 
    AS RANGE RIGHT FOR VALUES();
CREATE PARTITION SCHEME partition_ByMonth_Scheme
    AS PARTITION partition_ByMonth_Function
    ALL TO ([PRIMARY]);
GO
DECLARE 
     @PartitionDate datetime = '20200101'
    ,@EndDate datetime = '20210101';
WHILE @PartitionDate <= @EndDate
BEGIN
    ALTER PARTITION SCHEME partition_ByMonth_Scheme NEXT USED [PRIMARY];
    ALTER PARTITION FUNCTION partition_ByMonth_Function() SPLIT RANGE(@PartitionDate);
    SET @PartitionDate = DATEADD(month, 1, @PartitionDate);
END;
GO
CREATE TABLE [TEST].[TEST_PS_MONTH]
(
    [ID] [varchar](4) not null,
    [VALUE] [varchar](30) null,
    [ETL_Date] [datetime] not null, 
    CONSTRAINT AK_TEST_PS_MONTH UNIQUE NONCLUSTERED
        ([ID] ASC, [ETL_Date] ASC)
        WITH (PAD_INDEX= OFF) ON partition_ByMonth_Scheme([ETL_Date])
) ON partition_ByMonth_Scheme([ETL_Date]);
GO
CREATE CLUSTERED COLUMNSTORE INDEX [IDX_TEST_PS_MONTH]
ON TEST.TEST_PS_MONTH
WITH (DROP_EXISTING = OFF, COMPRESSION_DELAY = 0) ON partition_ByMonth_Scheme([ETL_Date])
GO

Example monthly maintenance script (remove oldest 2020-01-01 month and add new 2021-02-01 month):

DECLARE 
      @OldestPartitionDate datetime = '20200101'
    , @NewPartitionDate datetime = '20210201';
BEGIN
    TRUNCATE TABLE [TEST].[TEST_PS_MONTH] WITH(PARTITIONS ( $PARTITION.partition_ByMonth_Function(@OldestPartitionDate) ) );
    ALTER PARTITION FUNCTION partition_ByMonth_Function() MERGE RANGE(@OldestPartitionDate);
    ALTER PARTITION SCHEME partition_ByMonth_Scheme NEXT USED [PRIMARY];
    ALTER PARTITION FUNCTION partition_ByMonth_Function() SPLIT RANGE(@NewPartitionDate);
END;
GO

Upvotes: 1

Related Questions