Creating Partitions on a Table

Object Creation

1. File Group (with correct folder structure)
2. Partition Function
3. Partition Schema
4. Partition Table

Note: If the database is part of Always On, make sure the folder structure has been created on the secondary servers as well.

Create File Group and Associate File to File Group

ALTER DATABASE Test02
 ADD  FILEGROUP [fg201701_tbImageFpMatchARCH]
 GO
 ALTER DATABASE Test02 
 ADD FILE 
 (NAME = N'fg201701_tbTable', FILENAME = N'D:\MSSQL\DATA\Test02\fg201701_tbTable.ndf' , SIZE = 1000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
 TO FILEGROUP [fg201701_tbTable]
 GO
 ALTER DATABASE Test02
 ADD  FILEGROUP [fg201702_tbTable]
 GO
 ALTER DATABASE Test02 
 ADD FILE 
 (NAME = N'fg201702_tbTable', FILENAME = N'D:\MSSQL\DATA\Test02\fg201702_tbTable.ndf' , SIZE = 1000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
 TO FILEGROUP [fg201702_tbTable]

Create Partition Function

CREATE PARTITION FUNCTION pftbTable AS RANGE RIGHT FOR VALUES 

(

    '1/1/2017',

    '2/1/2017'

);

GO

Create Partition Schema

You must always have one extra filegroup in addition to the number of filegroups specified for the boundary values when you are creating partitions.

CREATE PARTITION SCHEME [pstbTable] AS PARTITION [pftbTable] TO 
 (
     [PRIMARY],
     fg201701_tbTable,
     fg201702_tbTable
 );
 GO

Create Table with Partition

CREATE TABLE [dbo].[tbTable]
 (
     biId        BIGINT      IDENTITY(1,1) NOT NULL,
     dYYYYMM         DATE            NOT NULL CONSTRAINT DF_tbTable_dYYYYMM DEFAULT (datefromparts(YEAR(GETDATE()),MONTH(GETDATE()),1))
 CONSTRAINT [PK_tbTable] PRIMARY KEY CLUSTERED      (     biId ASC, dYYYYMM ASC ) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)  ON [pstbTable](dYYYYMM),
 ) ON pstbTable (dYYYYMM);
 GO

Note: Partition should never be on iYYYYMM. It should be always dYYYYMM