Clustered Indexes

A clustered index is one in which the physical order of rows is the same as the indexed order of rows. The bottom, or leaf, level of a clustered index contains the actual data pages.

You can have only a single clustered index per table, so use this index wisely. UPDATE and DELETE operations are often accelerated by clustered indexes, since these operations require much reading. Typically, for a table that has at least one index, you should make one of those indexes a clustered index.

Consider using a clustered index for:

• Columns that contain a limited (but not tiny) number of unique values.

  For example, a state column of a customers table that contains 50 unique state abbreviations, such as WA, CA, and MO.

• Queries that return a range of values, using operators such as BETWEEN, >, >=, <, and <=.

  For example:

  SELECT * FROM sales
  WHERE ord_date BETWEEN '5/1/93' AND '6/1/93'

• Queries that return large results sets.

  For example:

  SELECT * FROM phonebook
  WHERE last_name = 'Smith'

When a significant number of rows are being inserted into a table, avoid putting a clustered index on a monotonically increasing column, such as an identity column, of that table. Insert performance can be degraded if you do this, because each inserted row must go at the end of the table, on the last data page of the table. While one row is being inserted into the last data page (thus locking the data page), all other inserted rows must wait in a queue until the current insert is complete.

The leaf-level pages of a clustered index are actual data pages, and the data pages are physically ordered on the hard disk using the logical order of the clustered index.