CODEWITHSUNDEEP
sql-serverdata-partitioning
husnain_sys
husnain_sys

Reputation: 571

Partition SQL table on monthly basis

I need to partition SQL table on monthly basis. So far i am able to create 12 partitions in Year-2015. But when year 2016 starts, all data started to pile up in last partition (December in my case). I need to place data of January-2016 in 1 partition (January in my case). I cannot make partitions for every year. Any suggestions?

Upvotes: 1

Views: 6766

Answers (2)

Omid Rahimi
Omid Rahimi

Reputation: 477

Partition Table Monthly Bases using Computed Column.

**step1 : Create FileGroup For 12 Month **

ALTER DATABASE yourDataBase ADD FILEGROUP January ALTER DATABASE yourDataBase ADD FILE ( NAME = N'January', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\January.ndf' ) TO FILEGROUP January

ALTER DATABASE yourDataBase ADD FILEGROUP February ALTER DATABASE yourDataBase ADD FILE ( NAME = N'February', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\February.ndf', SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP February and so on Until 12 Month.

step2 : Create Function

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

step3: Create SCheme

CREATE PARTITION SCHEME partition_scheme_ByMonth AS PARTITION partition_ByMonth TO (January, February, March, April, May, June, July, August, September, October, November, December);

step4: Table Partition

ALTER TABLE PartitionTableByMonth ADD PartitionColumn as MONTH(OrderDate) PERSISTED

step5: index

CREATE NONCLUSTERED INDEX IX_PartitionedTable_Pd ON PartitionTableByMonth (PartitionColumn ) ON partition_scheme_ByMonth(PartitionColumn )

Now yourTable Partitioning By month

Upvotes: 1

Dan Guzman
Dan Guzman

Reputation: 46193

Below is an example of how to create an incremental monthly partition for a RANGE RIGHT function, including test data.

CREATE DATABASE Test;
GO

USE Test
GO

--main table partition function (before start of next month)
CREATE PARTITION FUNCTION PF_Monthly(datetime2(0))
AS RANGE RIGHT FOR VALUES (
      '2015-01-01T00:00:00'
    , '2015-02-01T00:00:00'
    , '2015-03-01T00:00:00'
    , '2015-04-01T00:00:00'
    , '2015-05-01T00:00:00'
    , '2015-06-01T00:00:00'
    , '2015-07-01T00:00:00'
    , '2015-08-01T00:00:00'
    , '2015-09-01T00:00:00'
    , '2015-10-01T00:00:00'
    , '2015-11-01T00:00:00'
    , '2015-12-01T00:00:00'
    , '2016-01-01T00:00:00' --future empty partition
)
GO

--main table partition scheme
CREATE PARTITION SCHEME PS_Monthly
    AS PARTITION PF_Monthly
    ALL TO ( [PRIMARY] );
GO


--main partitioned table
CREATE TABLE dbo.MontylyPartitionedTable(
      PartitioningColumn datetime2(0)
    , OtherKeyColumn int NOT NULL
    , OtherData int NULL
    , CONSTRAINT PK_MontylyPartitionedTable PRIMARY KEY
        CLUSTERED (PartitioningColumn, OtherKeyColumn)
        ON PS_Monthly(PartitioningColumn)
    ) ON PS_Monthly(PartitioningColumn);
GO

---load 12M rows test data
WITH
    t4 AS (SELECT n FROM (VALUES(0),(0),(0),(0)) t(n))
    ,t256 AS (SELECT 0 AS n FROM t4 AS a CROSS JOIN t4 AS b CROSS JOIN t4 AS c CROSS JOIN t4 AS d)
    ,t16M AS (SELECT ROW_NUMBER() OVER (ORDER BY (a.n)) - 1 AS num FROM t256 AS a CROSS JOIN t256 AS b CROSS JOIN t256 AS c)
INSERT INTO dbo.MontylyPartitionedTable WITH (TABLOCKX) (PartitioningColumn, OtherKeyColumn, OtherData) 
SELECT DATEADD(month, num/1000000, '20150101'), num, num
FROM t16M
WHERE num < 12000000;
GO

CREATE PROCEDURE dbo.CreateMonthlyPartition
      @NewMonthStartDate datetime2(0) --partition boundary to create
/*

*/
AS

SET XACT_ABORT ON;

BEGIN TRY

    BEGIN TRAN;

    --acquire exclusive lock on main table to prevent deadlocking during partition maintenance
    DECLARE @result int = (SELECT TOP (0) 1 FROM dbo.MontylyPartitionedTable WITH (TABLOCKX));


    --add new partition for future data
    ALTER PARTITION SCHEME PS_Monthly
              NEXT USED [PRIMARY];
    ALTER PARTITION FUNCTION PF_Monthly()
        SPLIT RANGE (@NewMonthStartDate);
    --this will release the exclusve table lock but the data in the staging table temporarily unavailable
    COMMIT;

END TRY
BEGIN CATCH

    IF @@TRANCOUNT > 0 ROLLBACK;

    THROW;

END CATCH;
GO

--schedule this before the start of each new month to create a new monthly partition 2 months in advance
SELECT DATEADD(day, 1, DATEADD(month, 1, EOMONTH(GETDATE())));
DECLARE @NewMonthStartDate datetime2(0) = DATEADD(day, 1, DATEADD(month, 1, EOMONTH(GETDATE())));

EXEC dbo.CreateMonthlyPartition @NewMonthStartDate;
GO

Upvotes: 2

Related Questions