How to decide clusted index key set

Generally speaking it is said that every table must have a clustered index. But selecting index key set for clustered index is not easy.


So the following items may help you to select clustered index keys.

• Columns in the index keys must not be updated.
  Because if the clustered index key set is updated all the following data likely to be moved from it location accordingly. And since all the non-clustered indexes contain clustered index key set, when clustered index key set is updated, all the related non-clustered index key set is updated. So this is a cost.
• The set of keys must create a narrow index.
  All non-clustered indexes contain clustered index keys. If clustered index key size is narrow, than non-clustered index size is also narrow, means more I/O efficiency.
• The index must optimize wide queries.
  A clustered  index has all data.  When a query gets data with clustered index seek, it's cost is very optimum. If clustered index is used by lots of queries, this will be cost effective.
• Clustered index keys on columns with sequential inserts.
  Not to let clustered index be fragmented frequently, it is better to have sequential clustered index keys.
•  The clustered index must be unique.
  If is is not, uniqueifier is used to make it unique. This make index and data size bigger.

If you have a key set that has all these properties it best deserves to be clustered index.