Setting up a partitioned table for Dates Dimension

As far as setting up a business intelligence system goes; one thing is certain, you will always need a table to host your Dates Dimension.

Below is a simple example of a date dimension.

It uses MSSQL partitioning feature to split the data by months, I have found it to be helpful in avoiding 'hot areas' in the disk.  These are caused by having the same portion of the disk accessed constantly, and that becomes more relevant if you are writing as well.

It is possible to improve performance further by populating this table with all required information and then setting all filegroups used by it as READ-ONLY.  Furthermore, this table can be relocated to a different disk then the one used to store your fact tables.

The partition function below expects 13 file groups to split the data for dates dimension.

There is one caviat on this code is that its clustered key is not done on the primary key, as it is usually the case with MSSQL. Instead, a unique key (IX_DIM_DATES_UNQ) is created and it is partition using the same partition scheme function as the table (PS_DIM_DATES); and all other indexes on this table including the primary key index are stored on the first filegroup (DIM_DATES_M00)

-- =======================================
-- =======================================
-- DROPPING OBJECTS
-- =======================================
-- =======================================

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DIM_DATES]') AND type in (N'U'))
DROP TABLE [dbo].[DIM_DATES]
GO

IF  EXISTS (SELECT * FROM sys.partition_schemes WHERE name = N'PS_DIM_DATES')
DROP PARTITION SCHEME [PS_DIM_DATES]
GO

IF  EXISTS (SELECT * FROM sys.partition_functions WHERE name = N'PF_DIM_DATES')
DROP PARTITION FUNCTION [PF_DIM_DATES]
GO

-- =======================================
-- =======================================
-- RECREAING OBJECTS
-- =======================================
-- =======================================

CREATE PARTITION FUNCTION [PF_DIM_DATES](int) AS RANGE LEFT FOR VALUES (
  0
, 1, 2, 3, 4
, 5, 6, 7, 8
, 9, 10, 11, 12
)
GO

CREATE PARTITION SCHEME [PS_DIM_DATES] AS PARTITION [PF_DIM_DATES] all TO
(
[primary]
)
GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[DIM_DATES](
    [DATE_KEY] [int] NOT NULL,
    [DATE_VALUE] [datetime] NOT NULL,
    [DAY_OF_MONTH] [int] NOT NULL,
    MONTH_LBL_SHORT [varchar](14) NOT NULL,
    [CY_MONTH_KEY] [int] NOT NULL,
    [CY_MONTH_ORDER] [int] NOT NULL,
    [CY_MONTH_LBL_LONG] [varchar](14) NOT NULL,
    [CY_MONTH_LBL_SHORT] [varchar](14) NOT NULL,
    [CY_QUARTER_KEY] [int] NOT NULL,
    [CY_QUARTER_ORDER] [int] NOT NULL,
    [CY_QUARTER_LBL_LONG] [varchar](14) NOT NULL,
    [CY_QUARTER_LBL_SHORT] [varchar](14) NOT NULL,
    [CY_YEAR_KEY] [int] NOT NULL,
    [CY_YEAR_LBL] [varchar](14) NOT NULL,
    [FY_MONTH_KEY] [int] NOT NULL,
    [FY_MONTH_ORDER] [int] NOT NULL,
    [FY_MONTH_LBL_LONG] [varchar](14) NOT NULL,
    [FY_MONTH_LBL_SHORT] [varchar](14) NOT NULL,
    [FY_QUARTER_KEY] [int] NOT NULL,
    [FY_QUARTER_ORDER] [int] NOT NULL,
    [FY_QUARTER_LBL_LONG] [varchar](14) NOT NULL,
    [FY_QUARTER_LBL_SHORT] [varchar](14) NOT NULL,
    [FY_YEAR_KEY] [int] NOT NULL,
    [FY_YEAR_LBL] [varchar](14) NOT NULL,

CONSTRAINT [PK_DIM_DATES] PRIMARY KEY NONCLUSTERED
(
    [DATE_KEY] ASC
)
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 85)
ON [primary]
)
SET ANSI_PADDING OFF
GO

CREATE UNIQUE CLUSTERED INDEX [IX_DIM_DATES_UNQ] ON [dbo].[DIM_DATES]
(
    [DATE_KEY] ASC,
    [CY_MONTH_ORDER] ASC
)
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 85)
ON PS_DIM_DATES( [CY_MONTH_ORDER] )

CREATE UNIQUE NONCLUSTERED INDEX [IX_DIM_DATES_KEYS] ON [dbo].[DIM_DATES]
(
    [DATE_KEY] ASC,
    [CY_MONTH_KEY] ASC,
    [CY_QUARTER_KEY] ASC,
    [CY_YEAR_KEY] ASC,
    [FY_MONTH_KEY] ASC,
    [FY_QUARTER_KEY] ASC,
    [FY_YEAR_KEY] ASC,
    [DAY_OF_MONTH] ASC,
    [CY_MONTH_ORDER] ASC,
    [CY_QUARTER_ORDER] ASC,
    [FY_QUARTER_ORDER] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90)
ON PS_DIM_DATES( [CY_MONTH_ORDER] )

CREATE UNIQUE NONCLUSTERED INDEX [IX_DIM_DATES_LABELS] ON [dbo].[DIM_DATES]
(
    [DATE_KEY] ASC,
    [CY_MONTH_ORDER] ASC,
    [MONTH_LBL_SHORT] ASC,
    [CY_MONTH_LBL_LONG] ASC,
    [CY_MONTH_LBL_SHORT] ASC,
    [CY_QUARTER_LBL_LONG] ASC,
    [CY_QUARTER_LBL_SHORT] ASC,
    [CY_YEAR_LBL] ASC,
    [FY_MONTH_LBL_LONG] ASC,
    [FY_MONTH_LBL_SHORT] ASC,
    [FY_QUARTER_LBL_LONG] ASC,
    [FY_QUARTER_LBL_SHORT] ASC,
    [FY_YEAR_LBL] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 85)
ON PS_DIM_DATES( [CY_MONTH_ORDER] )