How to determine appropriate columns for clustered index on a table

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. 
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s