Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment