After creating the partition function, programmer needs to create a partition scheme to associate it with the partition function. Based on the boundary values defined in the partition function, there will be five partitions. The data of each partition is stored in a filegroup. You should have the same number of filegroups as partitions. If there are five partitions, you need to create five filegroups: fg1, fg2, fg3, fg4, fg5.
The following statements create the PSOrderDate partition scheme, associating it with the PFOrderDate partition function:
CREATE PARTITION SCHEME PSOrderDate
AS PARTITION PFOrderDate
TO (fg1, fg2, fg3, fg4, fg5)
The partition scheme, PSOrderDate, created in the preceding statement directs each partition to a separate filegroup.
CREATE CLUSTERED INDEX ix_SalesOrderID
ON Sales.MySalesOrderHeader (SalesOrderID)
ON PSOrderDate (OrderDate)
The preceding statement will distribute the table data into five filegroups based on the yearly data of orders stored in the OrderDate column.
The following statements create the PSOrderDate partition scheme, associating it with the PFOrderDate partition function:
CREATE PARTITION SCHEME PSOrderDate
AS PARTITION PFOrderDate
TO (fg1, fg2, fg3, fg4, fg5)
The partition scheme, PSOrderDate, created in the preceding statement directs each partition to a separate filegroup.
Creating a Clustered Index
After creating the partition scheme, you need to associate it with a clustered index. As the clustered index is created on the attribute having unique and non-null values, you can create the index on the SalesOrderID column of the SalesOrderHeader table. To create the partitioned index, you need to associate the clustered index with the partition scheme as follows:CREATE CLUSTERED INDEX ix_SalesOrderID
ON Sales.MySalesOrderHeader (SalesOrderID)
ON PSOrderDate (OrderDate)
The preceding statement will distribute the table data into five filegroups based on the yearly data of orders stored in the OrderDate column.