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, >, >=,