partition-right-dates.sql

use master;
GO

SET NOCOUNT ON;
GO

IF DB_ID('Partitioning') IS NOT NULL
BEGIN
	ALTER DATABASE Partitioning SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
	DROP DATABASE Partitioning;
END
GO

CREATE DATABASE Partitioning;
GO

ALTER DATABASE Partitioning ADD FILEGROUP [201911];
GO
ALTER DATABASE Partitioning ADD FILEGROUP [201912];
GO
ALTER DATABASE Partitioning ADD FILEGROUP [202001];
GO
ALTER DATABASE Partitioning ADD FILEGROUP [202002];
GO
ALTER DATABASE Partitioning ADD FILEGROUP [202003];
GO

ALTER DATABASE Partitioning
ADD FILE
    (
        NAME = FG201911,
        FILENAME = 'S:\MSSQL\Data\FG201911.ndf',
        SIZE = 64MB,
        FILEGROWTH = 256MB
    )
TO FILEGROUP [201911];
GO
ALTER DATABASE Partitioning
ADD FILE
    (
        NAME = FG201912,
        FILENAME = 'S:\MSSQL\Data\FG201912.ndf',
        SIZE = 64MB,
        FILEGROWTH = 256MB
    )
TO FILEGROUP [201912];
GO
ALTER DATABASE Partitioning
ADD FILE
    (
        NAME = FG202001,
        FILENAME = 'S:\MSSQL\Data\FG202001.ndf',
        SIZE = 64MB,
        FILEGROWTH = 256MB
    )
TO FILEGROUP [202001];
GO
ALTER DATABASE Partitioning
ADD FILE
    (
        NAME = FG202002,
        FILENAME = 'S:\MSSQL\Data\FG202002.ndf',
        SIZE = 64MB,
        FILEGROWTH = 256MB
    )
TO FILEGROUP [202002];
GO
ALTER DATABASE Partitioning
ADD FILE
    (
        NAME = FG202003,
        FILENAME = 'S:\MSSQL\Data\FG202003.ndf',
        SIZE = 64MB,
        FILEGROWTH = 256MB
    )
TO FILEGROUP [202003];
GO


USE Partitioning;
GO

CREATE PARTITION FUNCTION [pf_monthly_int] (DATE)
AS RANGE RIGHT FOR VALUES
(   '2019-11-01',
    '2019-12-01',
    '2020-01-01',
    '2020-02-01',
    '2020-03-01'
);
GO

CREATE PARTITION SCHEME [ps_monthly_int]
AS PARTITION [pf_monthly_int]
TO
(
    [PRIMARY],
    [201911],
    [201912],
    [202001],
    [202002],
    [202003]
);
GO

CREATE TABLE dbo.PartitionedTable
(
    UniqueCol BIGINT IDENTITY,
    PartitioningCol DATE NOT NULL,
    Col1 CHAR(256)
        DEFAULT ('Somevalue'),
    Col2 BIT
        DEFAULT (1),
    Col3 INT
        DEFAULT ('123')
) ON [ps_monthly_int] (PartitioningCol);
GO

CREATE UNIQUE CLUSTERED INDEX cx_PartitionedTable
ON dbo.PartitionedTable (
                            PartitioningCol,
                            UniqueCol
                        );
GO

INSERT dbo.PartitionedTable
(
    PartitioningCol
)
VALUES
('2019-11-01');
GO 5000
INSERT dbo.PartitionedTable
(
    PartitioningCol
)
VALUES
('2019-12-01');
GO 5000
INSERT dbo.PartitionedTable
(
    PartitioningCol
)
VALUES
('2020-01-01');
GO 5000