This is just an example.

Take note of the columns chosen as the indexing column, as well as partitioning column.

Be very clear that you know the difference between the partitioning and indexing column.

I chose to make my partitioning column the same across the table and the indexes to ensure the tables and indexes are partition aligned.

In the example, the partitions are all on the same filegroup since we did not need disk throughput, just needed to be able to drop old partitions (easy way of getting rid of old data).

CREATE PARTITION FUNCTION DateKeyRangeMyTable(INT) AS

RANGE RIGHT FOR VALUES

( 20100101,20110101,20120101,20130101,20140101,20150101,20160101,20170101,20180101,

20190101,20200101,20210101,20220101)

GO

CREATE PARTITION SCHEME CustomerScheme AS

PARTITION DateKeyRangeMyTable ALL TO

([DatamartTableFileGroup])

CREATE PARTITION SCHEME IX_CustomerScheme AS

PARTITION DateKeyRangeMyTable ALL TO

([DatamartIndexFileGroup])

CREATE TABLE [dbo].[CustomerPurchaseDetail](

[CUSTMRID] [bigint] Not NULL,

[DATE_CYCLE] [int] NULL,

[HighcardinalityID] [bigint] NULL

) ON CustomerScheme (DATE_CYC) ;

CREATE CLUSTERED INDEX CLIX_Customer ON CustomerPurchaseDetail

(DATE_CYC) WITH (FILLFACTOR = 100) ON CustomerScheme(DATE_CYCLE)

CREATE UNIQUE NONCLUSTERED INDEX [idx_CUSTMRID] ON [dbo].[CustomerPurchaseDetail]

(

[CUSTMRID] ASC

)WITH (FILLFACTOR = 100) ON IX_CustomerScheme(DATE_CYCLE)

GO