How to Create Partition Scheme and Clustered Index: SQL Server

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:

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:

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.


Popular posts from this blog

difference between structure and union in C Language

Difference between Linear search and Binary Search in c language

Difference between static and dynamic websites