In SQL Server 2008, just as you can divide tables into partitions.
Partitioned indexes are indexes in which the index pages are separated horizontally into multiple physical locations based on range of values in the index column. The physical locations for partitions are filegroups.
Partition indexes are use for the same reason that for partition tables: to improve performance and to make large indexes more manageable by enabling you to focus management tasks on individual partitions rather than on the entire index.
Index Alignment:-
An index that is partitioned in the same way as its table is referred to as being “aligned” with the table. A partitioned index is aligned if it meets the following rules:
- The partitioning key is compatible with that of the table.
- The index has the same number of partitions as the table.
- The range of values stored in the partitions matches those of the table.
Creating Partitioned Indexes:-
Creating an index on a partitioned table creates an aligned index. This approach should meet the needs of most scenarios.
- Create a partition function to specify how the index that uses the function can be partitioned.
- Create a partition scheme to specify the placement of the partitions of a partition function on filegroups.
Options to Maintain the index free space:-
FILLFACTOR:- Fillfactor option allows you to allocate a percentage (1 to 100) of free space on the leaf-level index pages to reduce page splitting. This percentage determines how much the leaf-level pages should be filled.
PAD_INDEX:- Pad_index option enables you to specify whether or not the fill factor applied to the leaf pages also applies to the non-leaf pages.