Why clustered index is important
Suppose we have a table where no indexes have been defined and we are inserting lots of records to the table. In this case, the data does not get inserted in any particular order into the data pages. As a result the SQL Server has to do more reads to access the requested data. On the other hand if we define a clustered index on the table then the data get inserted sequentially. So less disk I/O is required to access the requested data.
It is advised to create a clustered index with as few columns as possible. This is because, all nonclustered indexes use the key values from the clustered index as lookup keys. So any non-clustered index defined on that table wll also be larger.
Since only one clustered index can be created per table, so it is important to analyse properly before defining it. Queries running against the table needs to be considered properly before deciding
on the columns for clustered index.
Clustered index can be defined on
– Columns that return large range of values by using BETWEEN, >, >=, <, <= operators.
– COlumns that return sorted data using ORDER BY or GROup BY clause.
– Columns that contain a large number of distinct values.
– Columns that return data using JOIN clause.
– Columns that return large result sets.
Clustered Index are not good choice on
– Columns that undergo frequent changes. Changes to a clustered index mean that the entire row of data will be moved because the database engine keep the data values in a physical order. This is an important consideration in high-volume transaction processing systems where the data is volatile.
– Wide keys. As already discussed,non-clustered index also refer to the clustered index so any nonclustered indexes defined on the same table will also be larger.