I know the differences between clustered and non-clustered indexes. Can someone tell me the various scenarios where we actually prefer non clustered index over a clustered index ?

  • 2
    Is the question about a specific DBMS that has clustered indexes (eg. SQL Server, MySQL)? – ypercubeᵀᴹ Jun 27 '16 at 10:35

The following is written for SQL Server, but should be quite the same with other RDBMS...

The clustered index is physically sorted on the storage media and will cover all columns of your table - as if they were include columns.

A good clustered index is bound to a column with an implicit sort quality, such as insertDateTime or a running number (e.g. with an IDENTITY column). Any insertion is put at the end and is placed correctly at once. The worst clustered index is bound to a column with no sort order such as UNIQUEIDENIFIER. The effect will be a fragementation of 99.9%. (But a look on NEWSEQUENTIALID might be interesting...)

It is important to keep in mind, that other indexes use an existing clustered index as lookup-key. If this lookup is slow due to fragementation, other indexes will be slow too.

Imagine a UNIQUEIDENTIFIER column which you want to use as FK-column somewhere else. This was a case, where a non-clustered key would provide a much smaller index, better to maintain and better to rebuild. In case this was a clustered key the performance would most probably be very bad...

  1. If a table is large and growing fast, clustered index might become too expensive to maintain since the DB server has to reshuffle all the data while rebalancing the tree, not only nodes with key values. It might significantly affect speed of data modification queries.

    Clustered index stores table data right in the index (a tree node contains the whole record, not only the key). So when you rebalance the tree of this index, you also have to physically move records. In comparison, non-clustered indexes store trees separately from the main data and only use pointers to physical placement of corresponding records. So they are much cheaper to rebalance.

  2. For most DBMS (TokuDB for MySQL/MariaDB/Percona) is an exception) there can be only one clustered index per table because it defines the physical data placement. So, if you need several indexes on a table, you have to use non-clustered ones.

  • Can you please provide an example where multiple non clustered indexes will be there? – Rupsingh Mathwale Jun 27 '17 at 4:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.