A clustered index sorts and stores the data rows of the table in an order based on the clustered index key. The clustered index is implemented as a B-tree where every page in a tree is called an index node. Because a clustered index determines the order in which table rows are actually stored, each table can have only one clustered index, and the table's rows cannot be stored in more than one order.
Because you can have only one clustered index per table, you must ensure that you use it to achieve the maximum benefits. Before you create a clustered index, you need to understand how your data will be accessed.
Clustered indexes are most effective when used to support queries that do the following:
- Return a range of values by using operators such as
BETWEEN, >, >=, <, and <= - Return data sorted using the
ORDER BYorGROUP BYclause - Return data combined by using
JOINclauses; typically these are foreign key columns - Return large result sets