Before creating clustered indexes, understand how your data will be accessed. Consider using a clustered index for:
•Columns that contain a large number of distinct values.
•Queries that return a range of values using operators such as BETWEEN, >, >=, <, and <=.
•Columns that are accessed sequentially.
•Queries that return large result sets.
•Columns that are frequently accessed by queries involving join or GROUP BY clauses; typically these are foreign key columns. An index on the column(s) specified in the ORDER BY or GROUP BY clause eliminates the need for SQL Server to sort the data because the rows are already sorted. This improves query performance.
•OLTP-type applications where very fast single row lookup is required, typically by means of the primary key. Create a clustered index on the primary key.
Clustered indexes are not a good choice for:
•Columns that undergo frequent changes
This results in the entire row moving (because SQL Server must keep the data values of a row in physical order). This is an important consideration in high-volume transaction processing systems where data tends to be volatile.
The key values from the clustered index are used by all nonclustered indexes as lookup keys and therefore are stored in each nonclustered index leaf entry.
commented on Apr 1 2012 2:53AM