Clustered and non-clustered indexes

Index architectures are classified as clustered or non-clustered. Clustered indexes are indexes whose order of the rows in the data pages corresponds to the order of the rows in the index. This order is why only one clustered index can exist in any table, whereas, many non-clustered indexes can exist in the table. In some database systems, the leaf node of the clustered index corresponds to the actual data, not a pointer to data that is found elsewhere.

Both clustered and non-clustered indexes contain only keys and record identifiers in the index structure. The record identifiers always point to rows in the data pages. With clustered indexes, the database manager attempts to keep the data in the data pages in the same order as the corresponding keys in the index pages. Thus the database manager attempts to insert rows with similar keys onto the same pages. If the table is reorganized, data is inserted into the data pages in the order of the index keys. The database manager does not maintain any order of the data when compared to the order of the corresponding keys of a non-clustered index.

Reorganizing a table with a chosen index reclusters the data. A clustered index is most useful for columns that have range predicates because it allows better sequential access of data in the table. As a result, since like values are on the same data page, fewer pages are fetched.

In general, only one of the indexes in a table can have a high degree of clustering.

Clustering indexes can improve the performance of most query operations because they provide a more linear access path to data, which is stored in pages. In addition, because rows with similar index key values are stored together, sequential detection prefetching is more efficient when clustering indexes are used.

Clustering indexes cannot be specified as part of the table definition that is used with the CREATE TABLE statement. Instead, clustering indexes are only created by running the CREATE INDEX statement with the CLUSTER option specified. If you want to cluster the table on the primary key, the ALTER TABLE statement must be used to add a primary key that corresponds to the clustering index that was created on the table. This clustering index is then used as the table's primary key index.

Note: Setting PCTFREE in the table to an appropriate value with the ALTER TABLE statement can help the table remain clustered by leaving adequate free space to insert rows in the pages with similar values. For more information, see ALTER TABLE statement.

Improving performance with clustering indexes

Generally, clustering is more effectively maintained if the clustering index is unique.