Below things should be considered while creating clustered index on a table.
- Unique: Primary key columns that can be used to uniquely identify each row are ideal for clustered index key
- Identity columns of normalized tables based on business needs (Ex: empid for employee table, orderid for orders table) can be used as keys of clustered index
- Narrow: Clustered index key should be narrow as much as possible because it is included in all nonclustered indexes and is used for looking up data rows. A wide clustered index will increase the size of nonclustered indexes as well
- Static: Frequently updated columns are not suitable as clustered index key because whenever data changes, table has to reorganized to follow the order, nonclustered indexes also should be updated.
- Clustered index on date, identity columns will improve the performance of range queries.