Try OpenEdge Now
skip to main content
DataServer for Microsoft SQL Server
The DataServer Tutorial : Prime to ROWID identification : Recommendation for choosing best clustered index
 

Recommendation for choosing best clustered index

When selecting an index as the clustered index, consider the following guidelines:
*Choose a frequently searched index for query resolution, particularly by large queries, or as a foreign key on join relations (JOIN), or for sorting (GROUP BY, ORDER BY, etc.) and/or ROWID positioning inside the DataServer. For composite clustered keys, the first component column should be the most searched column of the key.
*Choose an index with a high degree of uniqueness.
*Choose an index that can be utilized in range queries to produce bracketed results.
*Choose an index that is often accessed sequentially. Rows are stored in the sorted order of this key, and the clustered index can act as a "covering" index, allowing data to be read out of the index itself rather than retrieving additional pages in a lookup operation to get the desired data values.
*Choose an index that is monotonic, incremental, and unique, such as an IDENTITY column, particularly if it is also meaningful to the application table content. An IDENTITY column is guaranteed to be incremental, and unique and the MS SQL Server optimizer can provide additional optimizations when an IDENTITY column is also the clustered index.
*Choose an index that has a narrowly sized key and preferably a single column, especially since non-clustered indexes use the clustered index to locate rows and hold the clustered key in their B-tree structure. A composite clustered key can provide benefits because the part of the table that is also the clustered index does not need additional space to create the clustered columns and its index.Choose an index that is reasonably static and does not undergo frequent changes. The primary key is a good clustered index candidate.