Note: Always use date type for partition column.
CREATE TABLE [dbo].[tbTable] ( biTableId BIGINT NOT NULL, dYYYYMM DATE NOT NULL CONSTRAINT DF_tbTable_dYYYYMM DEFAULT (DATEFROMPARTS(YEAR(GETDATE()),MONTH(GETDATE()),1)), CONSTRAINT [PK_tbTable] PRIMARY KEY CLUSTERED ( biTableId 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
Make sure the following are created for partition…
- Partition File Group (Folder structure has been created as well)
- Partition Function
- Partition Schema
- 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.