These are general guidelines for clustered indexes:
Most allpages-locked tables should have clustered indexes or use partitions to reduce contention on the last page of heaps.
In a high-transaction environment, the locking on the last page severely limits throughput.
If your environment requires a lot of inserts, do not place the clustered index key on a steadily increasing value such as an IDENTITY column.
Choose a key that places inserts on random pages to minimize lock contention while remaining useful in many queries. Often, the primary key does not meet this condition.
This problem is less severe on data-only-locked tables, but is a major source of lock contention on allpages-locked tables.
Clustered indexes provide very good performance when the key matches the search argument in range queries, such as:
where colvalue >= 5 and colvalue < 10
In allpages-locked tables, rows are maintained in key order and pages are linked in order, providing very fast performance for queries using a clustered index.
In data-only-locked tables, rows are in key order after the index is created, but the clustering can decline over time.
Other good choices for clustered index keys are columns used in order by clauses and in joins.
If possible, do not include frequently updated columns as keys in clustered indexes on allpages-locked tables.
When the keys are updated, the rows must be moved from the current location to a new page. Also, if the index is clustered, but not unique, updates are done in deferred mode.
