SQL Server Tips by Robin Schumacher

Clustered Indexes

There is plenty of debate on whether every table should have a clustered index. Many SQL Server gurus suggest that each table should indeed have a clustered index. But, how can one know what series of columns are best suited for a clustered index?

Since clustered indexes sort the underlying table data in the order defined in the index, columns that are the targets of queries that routinely return single or few rows should not be indexed. Instead, a clustered index should be created on one or more columns that are the object of range searches, because the requested data will be found on the same page or pages within close proximity. The end results of smart clustered index designs are reduced I/O and better query response times for queries that return a range of data.

Another benefit of clustered indexes is that they assist in reorganizing a table. Unlike other database engines, SQL Server has no command for reorganizing a heap table. It only works on indexes. So, if there is a badly fragmented table, the DBA is usually stuck with executing a table purge, copying all the data out, truncating the table, and copying all the data back in. This feat is not easy to accomplish in an environment with high amounts of enforced referential integrity.

If the table has a clustered index, the DBA can simply rebuild the clustered index, which reorganizes the data pages because the leaf level of the clustered index is the data.

The above book excerpt is from:

High-Performance SQL Server DBA
Tuning & Optimization Secrets

ISBN: 0-9761573-6-5
Robin Schumacher

 http://www.rampant-books.com/book_2005_2_sql_server_dba.htm