SELECT DATEFROMPARTS(YEAR(GETDATE()),MONTH(GETDATE()),1)

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…

  1. Partition File Group
  2. Partition Function
  3. Partition Schema
  4. Partition Table
Last modified: June 5, 2020

Author

Comments

Write a Reply or Comment